def retrive_user_details(self):
# cursor.execute("Use admin;")
cursor = query(self.server,"Use admin;")
# cursor.execute("select department_id, subject_id from users where username = '******';")
cursor = query(self.server,"select department_id, subject_id from users where username = '******';")
rec = cursor.fetchall()
self.curr_dept = rec[0][0]
self.curr_sub = rec[0][1]
def set_title_num(self):
# cursor.execute("Use "+ self.curr_dept +";")
cursor = query(self.server, "Use " + self.curr_dept + ";")
# cursor.execute("SELECT Title, NumberOfQuestions FROM question_papers where Qid='" + str(self.qid) + "'")
cursor = query(
self.server,
"SELECT Title, NumberOfQuestions FROM question_papers where Qid='"
+ str(self.qid) + "'")
rec = cursor.fetchall()
self.title = rec[0][0]
self.num = rec[0][1]
def get_latest_qid(self):
# cursor.execute("Use "+ self.curr_dept +";")
cursor = query(self.server, "Use " + self.curr_dept + ";")
# cursor.execute("SELECT Qid FROM question_papers ORDER BY Qid DESC LIMIT 1")
cursor = query(
self.server,
"SELECT Qid FROM question_papers ORDER BY Qid DESC LIMIT 1")
rec = cursor.fetchall()
if len(rec) == 0:
return False
return int(rec[0][0])
def retirive_qp_details(self):
# cursor.execute("Use "+ self.curr_dept +";")
cursor = query(self.server,"Use "+ self.curr_dept +";")
# cursor.execute("select * from question_papers where subject_id ='" + self.curr_sub + "';")
cursor = query(self.server,"select * from question_papers where subject_id ='" + self.curr_sub + "';")
rec = cursor.fetchall()
dic = {}
for row in rec:
dic[row[0]] = []
for i in range(2,len(row)):
dic[row[0]].append(row[i])
return dic
def retirive_questions(self, qid):
# cursor.execute("Use "+ self.curr_dept +";")
cursor = query(self.server,"Use "+ self.curr_dept +";")
# cursor.execute("Select * from qp" + str(qid) + ";")
cursor = query(self.server,"Select * from qp" + str(qid) + ";")
rec = cursor.fetchall()
lst = []
for row in rec:
temp = []
for col in row:
temp.append(col)
lst.append(temp[1:])
return lst
def test_p2_not_valid(self):
"""reportMatch() throws if player 2 is not valid"""
q = "TRUNCATE TABLE players;"
tools.query(q)
self.assertEqual(dummy_player(player_name="Fissh Fillay",
country="Playland"), 0)
q = "SELECT * FROM matches ORDER BY id LIMIT 1"
p = tools.query(q)
i1 = str(p[0][0])
self.assertEqual(dummy_player(player_name="Kulv Sangwich",
country="Playland"), 0)
q = "SELECT * FROM matches ORDER BY id LIMIT 1"
p = tools.query(q)
i2 = p[0][0]
i2 = str(i2 + 2)
with self.assertRaises(LookupError):
tournament.reportMatch(p1=i1, p2=i2)
def test_p1_not_valid(self):
"""reportMatch() throws if player 1 is not valid"""
q = "TRUNCATE TABLE players;"
tools.query(q)
self.assertEqual(dummy_player(player_name="Double Quarder",
country="Playland"), 0)
q = "SELECT * FROM matches ORDER BY id LIMIT 1"
p = tools.query(q)
i1 = p[0][0]
self.assertEqual(dummy_player(player_name="Big Mac Sauce",
country="Playland"), 0)
q = "SELECT * FROM matches ORDER BY id LIMIT 1"
p = tools.query(q)
i2 = str(p[0][0])
i1 = str(i1 + 2)
with self.assertRaises(LookupError):
tournament.reportMatch(p1=i1, p2=i2)
def test_option_edit(self):
"""editPlayer() edits player with new info provided"""
q = "SELECT * FROM matches ORDER BY id LIMIT 1"
r = tools.query(q)
s = str(r[0][0])
self.assertEquals(tournament.editPlayer(player=s,
new_name="Johan Bach",
new_country="Guam"), 0)
def get_depts(self):
# cursor.execute("select * from admin.departments;")
cursor = query(self.server, "select * from admin.departments;")
rec = cursor.fetchall()
dic = {}
for row in rec:
dic[row[0]] = row[1]
return dic
def create_new_paper(self, title, time, num):
val = (str(self.curr_sub), title, time, num)
self.title = title
# cursor.execute("Use " + self.curr_dept + ";")
cursor = query(self.server, "Use " + self.curr_dept + ";")
sql = "insert into question_papers (subject_id, Title, Time, NumberOfQuestions) \
values (%s, %s, %s, %s);"
cursor = query(self.server, sql, val)
self.server.commit()
self.qid = self.get_latest_qid()
self.num = num
sql = "create table if not exists qp" + str(self.qid) + " \
(q_num int, Description varchar(255), A varchar(255), B varchar(255), C varchar(255),\
D varchar(255), Answer varchar(255));"
cursor = query(self.server, sql)
def login(self, username, password, admin=True):
# val = (username, password)
if admin:
# cursor.execute("USE admin;")
cursor = query(self.server, "USE admin;")
else:
# cursor.execute("USE student;")
cursor = query(self.server, "USE student;")
sql = "select password from users where username= '******';"
cursor = query(self.server, sql)
rec = cursor.fetchall()
if len(rec) == 0:
return False
if rec[0][0] == password:
return True
else:
return False
def update_question(self, lst):
i = 1
for row in lst:
tup = tuple(row)
sql = "update qp" + str(self.qid) + " set Description = %s,\
A = %s, B = %s, C = %s, D = %s, Answer = %s where q_num = " + str(
i) + ";"
cursor = query(self.server, sql, tup)
i += 1
self.server.commit()
def get_subs(self, dept=None):
if dept == None:
dept = self.curr_dept
# cursor.execute("select * from " + dept +".subjects;")
cursor = query(self.server, "select * from " + dept + ".subjects;")
rec = cursor.fetchall()
dic = {}
for row in rec:
dic[row[0]] = row[1]
return dic
def register(self, username, password, dept, sub, admin=True):
if self.curr_dept == None or self.curr_sub == None:
return False
val = (username, password, dept, sub)
if admin:
# cursor.execute("USE admin;")
cursor = query(self.server, "USE admin;")
else:
# cursor.execute("USE student;")
cursor = query(self.server, "USE student;")
sql = "select username from users where username= '******';"
cursor = query(self.server, sql)
rec = cursor.fetchall()
if len(rec) != 0:
return False
sql = "INSERT INTO users (username, password, department_id, subject_id) VALUES (%s, %s, %s, %s)"
cursor = query(self.server, sql, val)
self.server.commit()
return True
def get_total_subs(self):
depts = self.get_depts()
dic = {}
for key, val in depts.items():
dic[val] = []
# cursor.execute("SELECT SCHEMA_NAME \
# FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" + key + "';")
cursor = query(
self.server, "SELECT SCHEMA_NAME \
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '"
+ key + "';")
rec = cursor.fetchall()
if len(rec) == 0:
continue
# cursor.execute("use " + key + ";")
cursor = query(self.server, "use " + key + ";")
subs = self.get_subs(key)
for key1, val1 in subs.items():
dic[val].append(val1)
return dic
def db_init(self):
cursor = query(
self.server,
"SELECT * FROM admin.departments where department_id = '" +
str(self.curr_dept) + "';")
# cursor.execute("SELECT * FROM admin.departments where department_id = '" + str(self.curr_dept) + "';")
rec = cursor.fetchall()
if len(rec) == 0:
return False
cursor = query(
self.server,
"create database if not exists " + str(self.curr_dept) + ";")
# cursor.execute("create database if not exists " + str(self.curr_dept) + ";")
cursor = query(self.server, "Use " + str(self.curr_dept) + ";")
# cursor.execute("Use " + str(self.curr_dept) + ";")
sql = "create table if not exists question_papers (Qid int NOT NULL \
AUTO_INCREMENT, subject_id varchar(255), Title varchar(255) NOT NULL, Time int, NumberOfQuestions int, PRIMARY KEY (Qid));"
cursor = query(self.server, sql)
sql = "create table if not exists subjects (subject_id varchar(255), name varchar(255) NOT NULL, PRIMARY KEY (subject_id));"
cursor = query(self.server, sql)
def insert_question(self, lst):
i = 1
for row in lst:
val = "('" + str(i) + "',"
for col in row:
val += "'" + str(col) + "',"
val = val[:-1]
val += ")"
sql = "insert into qp" + str(self.qid) + " values " + val
cursor = query(self.server, sql)
i += 1
self.server.commit()
from tools import query, commit
x = query("show tables;")
print(x)
def test_display_zero_matches(self):
"""listPlayers() returns 1 if the tournament.Players table is empty"""
q = "TRUNCATE TABLE players;"
tools.query(q)
self.assertEqual(tournament.listPlayers(), 1)
def test_no_players(self):
"""swissPairings() throws if there are no players in the database"""
q = "TRUNCATE TABLE players;"
tools.query(q)
with self.assertRaises(ValueError):
tournament.swissPairings()
def test_option_delete(self):
"""editPlayer() deletes player"""
q = "SELECT * FROM matches ORDER BY id LIMIT 1"
r = tools.query(q)
s = str(r[0][0])
self.assertEquals(tournament.deletePlayer(player=s), 0)
