def verify(self): """ Returns: True if the user's credentials match the database. False otherwise. """ if isUniqueEmail(self.email) == False: # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT email, password FROM users WHERE email = %s;''' cur.execute(query, self.email) em = "" ps = "" value = False for tup in cur: em = tup[0] ps = tup[1] if em == self.email and ps == self.password: value = True # commit query db.commit() db.close() return value return False
def get_total_matches(version, region='all'): ''' Args: version: Version of the match region: Region of the match Returns: The total number of matches that happened in this region and version. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() if region == 'all': query = '''SELECT COUNT(*) FROM game WHERE version = %s;''' cur.execute(query, version) else: query = '''SELECT COUNT(*) FROM game WHERE region = %s AND version = %s;''' data = (region, version) cur.execute(query, data) for tup in cur: count = int(tup[0]) # commit query db.commit() db.close() return count
def save(self): ''' Returns: The Vote object. ''' User(self.email, "test").save() if isUnique(self.email, self.rid): # Get new database instance db = credentials.getDatabase() cur = db.cursor() # # generate votes # query = '''INSERT INTO transactions (uid, rid, vote, creation) # VALUES(%s, %s, %s, %s);''' # data = (self.email, self.rid, self.vote, self.creation.strftime('%Y-%m-%d %H:%M:%S')) query = '''INSERT INTO transactions (uid, rid, vote) VALUES(%s, %s, %s);''' data = (self.email, self.rid, self.vote) cur.execute(query, data) # commit query db.commit() db.close() else: #update # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''UPDATE transactions SET vote = %s WHERE uid = %s AND rid = %s;''' data = (self.vote, self.email, self.rid) cur.execute(query, data) # commit query db.commit() db.close() return self.__json__()
def save(self): """ Saves this Restaurant to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO restaurants VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);''' data = (self.rid, self.name, self.address, self.city, self.state, self.zip_code, self.phone, self.website, self.twitter, self.lat, self.lng) cur.execute(query, data) # commit query db.commit() db.close()
def save(self): """ Saves this ParticipantStat to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO participant_stat (match_id, participant_id, kills, deaths, assists, magic_damage, magic_damage_champs, magic_damage_taken, champ_level, gold_earned, win) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);''' data = (self.match_id, self.participant_id, self.kills, self.deaths, self.assists, self.magic_damage, self.magic_damage_champs, self.magic_damage_taken, self.champ_level, self.gold_earned, self.win) cur.execute(query, data) # commit query db.commit() db.close() return True
def save(self): """ Saves this Champion to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO champion (id, name, title) VALUES(%s, %s, %s);''' data = (self.champion_id, self.name, self.title) cur.execute(query, data) # commit query db.commit() db.close() return True
def load_all(): ''' Returns: A list of all items. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM item;''' cur.execute(query) items = [] for tup in cur: items.append(Item(tup[0], tup[1], tup[2], tup[3])) # commit query db.commit() db.close() return items
def save(self): """ Saves this ParticipantItem to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO participant_item (match_id, participant_id, item, slot, win) VALUES(%s, %s, %s, %s, %s);''' data = (self.match_id, self.participant_id, self.item, self.slot, self.win) cur.execute(query, data) # commit query db.commit() db.close() return True
def save(self): """ Saves this User to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO users (email, password) VALUES(%s, %s);''' data = (self.email, self.password) cur.execute(query, data) # commit query db.commit() db.close() return True
def save(self): """ Saves this Item to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO item (id, name, description, item_group) VALUES(%s, %s, %s, %s);''' data = (self.item_id, self.name, self.desc, self.item_group) cur.execute(query, data) # commit query db.commit() db.close() return True
def loadAll(): ''' Returns: A list of all users. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT email, password FROM users;''' cur.execute(query) users = [] for tup in cur: users.append(User(tup[0], tup[1])) # commit query db.commit() db.close() return users
def isUniqueEmail(email): ''' Args: email: the email to query Returns: True if the email does not yet exist in the database. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT COUNT(*) FROM users WHERE email =%s;''' cur.execute(query, email) count = 0 for tup in cur: count = tup[0] return count == 0
def save_banned_champ(self, champ_id): """ Saves a champ_id to the database as a banned champion. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO banned_champion (match_id, champion_id) VALUES(%s, %s);''' data = (self.match_id, champ_id) cur.execute(query, data) # commit query db.commit() db.close() return True
def load_all(): ''' Returns: A list of all champions. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM champion;''' cur.execute(query) champs = [] for tup in cur: champs.append(Champion(tup[0], tup[1], tup[2])) # commit query db.commit() db.close() return champs
def loadAll(): """ Returns: A list of all restaurants. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM restaurants;''' cur.execute(query) result = [] for tup in cur: result.append(Restaurant(tup[0], tup[1], tup[2], tup[3], tup[4], tup[5], tup[6], tup[7], tup[8], tup[9], tup[10])) # commit query db.commit() db.close() return result
def load(item_id): ''' Args: item_id: The id of the item to query Returns: An Item object. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM item WHERE id = %s;''' cur.execute(query, champion_id) i = "" for tup in cur: i = Item(tup[0], tup[1], tup[2], tup[3]) # commit query db.commit() db.close() return i
def load(champion_id): ''' Args: champion_id: The id of the champion to query Returns: A champion object. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM champion WHERE id = %s;''' cur.execute(query, champion_id) c = "" for tup in cur: c = Champion(tup[0], tup[1], tup[2]) # commit query db.commit() db.close() return c
def load(match_id): ''' Args: match_id: The id of the match Returns: A Match object ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM game WHERE id = %s;''' cur.execute(query, match_id) m = "" for tup in cur: m = Match(tup[0], tup[1], tup[2], tup[3]) # commit query db.commit() db.close() return m
def isUnique(email, rid): ''' Args: email: the user id rid: the restaurant id Returns: True if a vote has not yet been submitted by the user for the restaurant. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT COUNT(*) FROM transactions WHERE uid =%s AND rid = %s;''' data = (email, rid) cur.execute(query, data) count = 0 for tup in cur: count = tup[0] return count == 0
def load(email): ''' Args: email: The email to query. Returns: A user given the email. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM users WHERE email = %s;''' cur.execute(query,email) user = "" for tup in cur: user = User(tup[0], tup[1]) # commit query db.commit() db.close() return user
def save(self): """ Saves this Match to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO game (id, version, duration, region) VALUES(%s, %s, %s, %s);''' data = (self.match_id, self.version, self.duration, self.region) cur.execute(query, data) # commit query db.commit() db.close() for champ_id in self.banned_champs: self.save_banned_champ(champ_id) return True
def save(self): """ Saves this Participant to the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''INSERT IGNORE INTO participant (match_id, participant_id, champion_id, team_id, win, lane) VALUES(%s, %s, %s, %s, %s, %s);''' data = (self.match_id, self.participant_id, self.champion_id, self.team_id, self.win, self.lane) cur.execute(query, data) # commit query db.commit() db.close() self.save_inventory() self.save_stats() return True
def load(match_id, participant_id): ''' Args: match_id: The id of the match participant_id: The id of the participant Returns: An Participant object. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM participant WHERE match_id = %s AND participant_id = %s;''' cur.execute(query, match_id, participant_id) p = "" for tup in cur: p = Participant(tup[0], tup[1], tup[2], tup[3], tup[4], tup[5]) # commit query db.commit() db.close() return p
def get_ban_rate(champion_id, version, region): ''' Args: champion_id: The id of the champion version: Version of the game region: region of the game Returns: The champions's ban rate. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT C.id, C.name, COUNT(*) FROM champion C, banned_champion BC, game G WHERE BC.match_id=G.id AND BC.champion_id=C.id AND C.id = %s AND G.version = %s AND G.region = %s;''' data = (champion_id, version, region) cur.execute(query, data) champion_name = "N/A" count = 0 for tup in cur: champion_name = str(tup[1]) count = int(tup[2]) # build response object champ = {} champ['id'] = champion_id champ['name'] = champion_name champ['number'] = count # commit query db.commit() db.close() return champ
def get_all_ban_rates(version, region): ''' Args: version: version of the game region: region of the game Returns: All the champions ban rates. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT C.id, C.name, COUNT(*) FROM champion C, banned_champion BC, game G WHERE BC.match_id=G.id AND BC.champion_id=C.id AND G.version = %s AND G.region = %s GROUP BY C.id ORDER BY COUNT(*) DESC;''' data = (version, region) cur.execute(query, data) champions = [] for tup in cur: # build each record as a reponse object champ = {} champ['id'] = int(tup[0]) champ['name'] = str(tup[1]) champ['number'] = int(tup[2]) if champ['number'] != 0: champ['ban_rate'] = round(float(champ['number']) / float(match.get_total_matches(version, region)), 2) else: champ['ban_rate'] = 0 champions.append(champ) # commit query db.commit() db.close() return champions
def load(rid): """ Args: rid: restaurant id Returns: The restaurant object with this id. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM restaurants WHERE rid = %s;''' cur.execute(query, rid) rest = "" for tup in cur: rest = Restaurant(tup[0], tup[1], tup[2], tup[3], tup[4], tup[5], tup[6], tup[7], tup[8], tup[9], tup[10]) # commit query db.commit() db.close() return rest
def get_win_rate(item_id, version, region): ''' Args: item_id: The id of the item version: version of the game region: region of the game Returns: The item's win rate. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT I.id, I.name, COUNT(*) FROM item I, participant_item PI, game G WHERE PI.match_id=G.id AND I.id=PI.item AND PI.win=1 AND I.id = %s AND G.version = %s AND region = %s GROUP BY I.id ORDER BY COUNT(*) DESC;''' data = (item_id, version, region) cur.execute(query, data) item_name = "N/A" count = 0 for tup in cur: item_name = str(tup[1]) count = int(tup[2]) # build response object item = {} item['id'] = item_id item['name'] = item_name item['number'] = count # commit query db.commit() db.close() return item
def isUnique(rid): """ Args: rid: restaurant id Returns: True if the restaurant id does not exist in the database. """ # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT COUNT(*) FROM restaurants WHERE rid=%s;''' cur.execute(query, [rid]) result = 0 for tup in cur: result = tup[0] # commit query db.commit() db.close() return result == 0
def load(email, rid): ''' Args: email: The user id. rid: The restaurant id. Returns: A vote given the email and rid. ''' # Get new database instance db = credentials.getDatabase() cur = db.cursor() query = '''SELECT * FROM transactions WHERE uid = %s AND rid = %s;''' data = (email, rid) cur.execute(query,data) vote = "" for tup in cur: vote = Vote(tup[0], tup[1], tup[2], tup[3]) # commit query db.commit() db.close() return vote
def construct(ll, query, fltr): fltr = fltr.lower() # get local credentials cr = credentials.getCredentials() # get list of rids that match this query around the location ids = parse_url.parse(cr.client_id, cr.client_secret, ll, query) if len(ids) == 0: return None in_clause = "(" for i in ids: in_clause += "'"; in_clause += str(i) in_clause += "'"; in_clause += ", " in_clause = in_clause[:-2] in_clause += ")" db = credentials.getDatabase() cur = db.cursor() query = "" if fltr == 'top': #top rid votes query = "SELECT R.rid, SUM(T.vote) FROM restaurants R LEFT JOIN transactions T ON R.rid=T.rid WHERE R.rid IN " + in_clause + " GROUP BY R.rid ORDER BY SUM(T.vote) DESC" elif fltr == 'hot': # Most votes in the last 7 days query = "SELECT R.rid, HOT.total_votes FROM restaurants R LEFT JOIN (SELECT T.rid as hid, SUM(T.vote) AS total_votes FROM transactions T WHERE T.creation > DATE_SUB(NOW(), INTERVAL 14 DAY) GROUP BY T.rid ORDER BY SUM(T.vote)) as HOT ON R.rid=HOT.hid WHERE R.rid IN " + in_clause + " GROUP BY R.rid ORDER BY HOT.total_votes DESC;" # query = '''SELECT R.rid, HOT.total_votes # FROM restaurants R # LEFT JOIN ( # SELECT T.rid as hid, SUM(T.vote) AS total_votes # FROM transactions T # WHERE T.creation > DATE_SUB(NOW(), INTERVAL 14 DAY) # GROUP BY T.rid # ORDER BY SUM(T.vote) # ) as HOT # ON R.rid=HOT.hid # WHERE R.rid IN ? # GROUP BY R.rid # ORDER BY HOT.total_votes DESC;''' elif fltr == 'not': # Opposite of hot query = "SELECT R.rid, HOT.total_votes FROM restaurants R LEFT JOIN (SELECT T.rid as hid, SUM(T.vote) AS total_votes FROM transactions T WHERE T.creation > DATE_SUB(NOW(), INTERVAL 14 DAY) GROUP BY T.rid ORDER BY SUM(T.vote)) as HOT ON R.rid=HOT.hid WHERE R.rid IN " + in_clause + " GROUP BY R.rid ORDER BY HOT.total_votes ASC;" elif fltr == 'new': # The most recently voted on rids query = "SELECT R.rid, SUM(T.vote) FROM restaurants R LEFT JOIN transactions T ON R.rid=T.rid WHERE T.rid IN " + in_clause + " GROUP BY R.rid ORDER BY T.creation DESC;" elif fltr == 'old': # The most neglected rids query = "SELECT R.rid, SUM(T.vote) FROM restaurants R LEFT JOIN transactions T ON R.rid=T.rid WHERE T.rid IN " + in_clause + " GROUP BY R.rid ORDER BY T.creation ASC;" elif fltr == 'bot': # opposite of top query = "SELECT R.rid, SUM(T.vote) FROM restaurants R LEFT JOIN transactions T ON R.rid=T.rid WHERE R.rid IN " + in_clause + " GROUP BY R.rid ORDER BY SUM(T.vote) ASC" else: # return all restaurants we get from this call query = "SELECT R.rid, SUM(T.vote) FROM restaurants R LEFT JOIN transactions T ON R.rid=T.rid WHERE R.rid IN " + in_clause + " GROUP BY R.rid ORDER BY SUM(T.vote) DESC" cur.execute(query) restaurants = [] for tup in cur: rid = tup[0] count = tup[1] if count == None: count = 0 rest_dict = restaurant.load(rid).__json__() rest_dict["votes"] = str(count) # construct new restaurant restaurants.append(rest_dict) db.commit() db.close() print("Result size: ", len(restaurants)) return restaurants