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)