def get_averages_of_latest_matches(matches_requested):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT
radiant_win, avg(kills), avg(deaths), avg(assists), avg(last_hits), avg(denies), avg(gold_per_min),
avg(xp_per_min), avg(hero_damage), avg(tower_damage), avg(hero_healing), avg(remaining_gold) +
avg(gold_spent)
FROM 'matches'
ORDER BY match_date_time DESC
LIMIT {}
'''
cursor.execute(SQLQuery.format(matches_requested))
data = cursor.fetchone()
if debug:
print('Average Kills:', round(data[1]))
print('Average Deaths:', round(data[2]))
print('Average Assists:', round(data[3]))
print('Average Last Hits:', round(data[4]))
print('Average Denies:', round(data[5]))
print('Average GPM:', round(data[6]))
print('Average XPM:', round(data[7]))
print('Average Hero Damage:', round(data[8]))
print('Average Tower Damage:', round(data[9]))
print('Average Hero Healing:', round(data[10]))
print('Average Net Worth:', round(data[11]))
cursor.close()
connection.close()
def check_tables_exists():
tables_required = ['heroes', 'matches', 'items']
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT COUNT(*)
FROM sqlite_master
WHERE type = ? AND name = ?
'''
for table_name in tables_required:
cursor.execute(SQLQuery, ('table', table_name))
if cursor.fetchone()[0] == 1:
if debug:
print('The table named {} exists'.format(table_name))
elif table_name == 'heroes':
create_heroes_table()
elif table_name == 'matches':
create_matches_table()
elif table_name == 'items':
create_items_table()
else:
print(table_name)
cursor.close()
connection.close()
def add_item(item_id, item_name, item_cost, item_secret_shop, item_side_shop,
item_recipe):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
INSERT INTO items(item_id, item_name, item_cost, item_secret_shop,
item_side_shop, item_recipe)
VALUES (:item_id, :item_name, :item_cost, :item_secret_shop,
:item_side_shop, :item_recipe)
'''
cursor.execute(
SQLQuery, {
'item_id': item_id,
'item_name': item_name,
'item_cost': item_cost,
'item_secret_shop': item_secret_shop,
'item_side_shop': item_side_shop,
'item_recipe': item_recipe
})
if debug:
print('The item id {} was added'.format(item_id))
cursor.close()
connection.commit()
connection.close()
def check_hero_exists(hero_id):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT COUNT(*)
FROM 'heroes'
WHERE hero_id = :hero_id
'''
cursor.execute(SQLQuery, {'hero_id': hero_id})
if cursor.fetchone()[0] == 1:
cursor.close()
connection.close()
if debug:
print('The hero id {} exists'.format(hero_id))
return True
if debug:
print('The hero id {} does not exists'.format(hero_id))
cursor.close()
connection.close()
return False
def check_item_exists(item_id):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT COUNT(*)
FROM 'items'
WHERE item_id = :item_id
'''
cursor.execute(SQLQuery, {'item_id': item_id})
if cursor.fetchone()[0] == 1:
cursor.close()
connection.close()
if debug:
print('The item id {} already exists'.format(item_id))
return True
if debug:
print('The item id {} does not exists'.format(item_id))
cursor.close()
connection.close()
return False
def create_heroes_table():
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
CREATE TABLE heroes (hero_id INTEGER PRIMARY KEY, hero_name TEXT)
'''
cursor.execute(SQLQuery)
cursor.close()
connection.commit()
connection.close()
def add_hero(hero_id, hero_name):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
INSERT INTO heroes VALUES(?, ?)
'''
cursor.execute(SQLQuery, (hero_id, hero_name))
if debug:
print('The hero id {} was added'.format(hero_id))
cursor.close()
connection.commit()
connection.close()
def create_items_table():
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
CREATE TABLE items (
item_id INTEGER PRIMARY KEY,
item_name TEXT,
item_cost INTEGER,
item_secret_shop INTEGER,
item_side_shop INTEGER,
item_recipe INTEGER
)'''
cursor.execute(SQLQuery)
cursor.close()
connection.commit()
connection.close()
def get_latest_number_of_items_from_database():
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT count(item_id)
FROM 'items'
'''
cursor.execute(SQLQuery)
number_of_items = cursor.fetchone()
cursor.close()
connection.close()
return number_of_items[0]
def create_matches_table():
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
CREATE TABLE matches (
match_number INTEGER PRIMARY KEY,
match_id INTEGER,
game_mode INTEGER,
radiant_win INTEGER,
dire_team INTEGER,
hero_id INTEGER,
hero_level INTEGER,
kills INTEGER,
deaths INTEGER,
assists INTEGER,
last_hits INTEGER,
denies INTEGER,
gold_per_min INTEGER,
xp_per_min INTEGER,
hero_damage INTEGER,
tower_damage INTEGER,
hero_healing INTEGER,
remaining_gold INTEGER,
gold_spent INTEGER,
item_list TEXT,
backpack_list TEXT,
hero_abilities TEXT,
first_blood_time INTEGER,
match_duration INTEGER,
match_seq_num INTEGER,
leaver_status INTEGER,
match_date_time timestamp,
inserted_date_time timestamp
)'''
cursor.execute(SQLQuery)
cursor.close()
connection.commit()
connection.close()
def get_recent_matches(matches_requested):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT match_id
FROM 'matches'
ORDER BY match_date_time DESC
LIMIT {}
'''
cursor.execute(SQLQuery.format(matches_requested))
match_ids = cursor.fetchall()
cursor.close()
connection.close()
for match_id in match_ids:
get_match_result(match_id[0])
def get_latest_match_id():
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT match_id
FROM 'matches'
ORDER BY match_date_time DESC
LIMIT 1
'''
cursor.execute(SQLQuery)
match_id = cursor.fetchone()
cursor.close()
connection.close()
return match_id[0]
def get_match_result(match_id):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
SELECT *
FROM 'matches'
WHERE match_id = :match_id
'''
cursor.execute(SQLQuery, {'match_id': match_id})
match_data = cursor.fetchone()
cursor.close()
connection.close()
match_number = match_data[0]
match_id = match_data[1]
game_mode = match_data[2]
radiant_win = match_data[3]
hero_id = match_data[4]
hero_level = match_data[5]
kills = match_data[6]
deaths = match_data[7]
assists = match_data[8]
last_hits = match_data[9]
denies = match_data[10]
gold_per_min = match_data[11]
xp_per_min = match_data[12]
hero_damage = match_data[13]
tower_damage = match_data[14]
hero_healing = match_data[15]
remaining_gold = match_data[16]
gold_spent = match_data[17]
item_list = ast.literal_eval(match_data[18])
backpack_list = ast.literal_eval(match_data[19])
hero_abilities = ast.literal_eval(match_data[20])
first_blood_time = match_data[21]
match_duration = match_data[22]
match_seq_num = match_data[23]
leaver_status = match_data[24]
match_date_time = match_data[25]
inserted_date_time = match_data[26]
if debug:
print('====== MATCH: ' + str(match_id) + " RESULTS ======")
print('Match number:', match_number)
print('Match id:', match_id)
print('Game mode:', game_mode)
print('Radiant win', radiant_win)
print('Hero id:', hero_id)
print('Hero level:', hero_level)
print('Kills:', kills)
print('Deaths:', deaths)
print('Assists:', assists)
print('Last hits:', last_hits)
print('Denies:', denies)
print('Gold per min:', gold_per_min)
print('Xp per min:', xp_per_min)
print('Hero damage:', hero_damage)
print('Tower damage:', tower_damage)
print('Hero healing:', hero_healing)
print('Remaining gold:', remaining_gold)
print('Gold spent:', gold_spent)
print('Item list:', item_list)
print('Backpack list:', backpack_list)
print('Hero abilities:', hero_abilities)
print('First blood time:', first_blood_time)
print('Match duration:', match_duration)
print('Match seq num:', match_seq_num)
print('Leaver status:', leaver_status)
print('Match date time:', match_date_time)
print('Inserted date time:', inserted_date_time)
def add_match(match_id, game_mode, radiant_win, dire_team, hero_id, hero_level,
kills, deaths, assists, last_hits, denies, gold_per_min,
xp_per_min, hero_damage, tower_damage, hero_healing,
remaining_gold, gold_spent, item_list, backpack_list,
hero_abilities, first_blood_time, match_duration, match_seq_num,
leaver_status, match_date_time):
connection = sqlite3.connect(database)
cursor = connection.cursor()
SQLQuery = '''
INSERT INTO matches(match_id, game_mode, radiant_win, dire_team,
hero_id, hero_level, kills, deaths, assists, last_hits, denies,
gold_per_min, xp_per_min, hero_damage, tower_damage, hero_healing,
remaining_gold, gold_spent, item_list, backpack_list, hero_abilities,
first_blood_time, match_duration, match_seq_num, leaver_status,
match_date_time, inserted_date_time)
VALUES (:match_id, :game_mode, :radiant_win, :dire_team, :hero_id,
:hero_level, :kills, :deaths, :assists, :last_hits, :denies, :gold_per_min,
:xp_per_min, :hero_damage, :tower_damage, :hero_healing, :remaining_gold,
:gold_spent, :item_list, :backpack_list, :hero_abilities,
:first_blood_time, :match_duration, :match_seq_num, :leaver_status,
:match_date_time, :inserted_date_time)
'''
# Converts the Unix time to human readable date and time
match_date_time = str(
datetime.utcfromtimestamp(match_date_time).strftime(
'%Y-%m-%d %H:%M:%S'))
inserted_date_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute(
SQLQuery, {
'match_id': match_id,
'game_mode': game_mode,
'radiant_win': radiant_win,
'dire_team': dire_team,
'hero_id': hero_id,
'hero_level': hero_level,
'kills': kills,
'deaths': deaths,
'assists': assists,
'last_hits': last_hits,
'denies': denies,
'gold_per_min': gold_per_min,
'xp_per_min': xp_per_min,
'hero_damage': hero_damage,
'tower_damage': tower_damage,
'hero_healing': hero_healing,
'remaining_gold': remaining_gold,
'gold_spent': gold_spent,
'item_list': item_list,
'backpack_list': backpack_list,
'hero_abilities': hero_abilities,
'first_blood_time': first_blood_time,
'match_duration': match_duration,
'match_seq_num': match_seq_num,
'leaver_status': leaver_status,
'match_date_time': match_date_time,
'inserted_date_time': inserted_date_time
})
if debug:
print('The match id {} was added'.format(match_id))
cursor.close()
connection.commit()
connection.close()
