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 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 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 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 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 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 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 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 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 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 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 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 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
