CompetitiveGamingClubOverwatchBot/DatabaseConnection.py at main · zyplos/CompetitiveGamingClubOverwatchBot · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
import sqlite3
from sqlite3 import Error
from Player import Player
import datetime
from APIConnection import APIConnection

class DatabaseConnection:
    def __init__(self):
        try:
            self.__db = sqlite3.connect('OverwatchDB.db')
        except Error as e:
            print(e)

    def getAllPlayers(self):
        # init
        playersList = []
        cursor = self.__db.cursor()

        # get list of players from database
        cursor.execute('SELECT * FROM players')
        result = cursor.fetchall()
        cursor.close()

        # construct players list
        for item in result:
            playersList.append(
                Player(item[0], item[1], item[2], item[3], item[4], item[5], None))

        # update newSR
        for player in playersList:
            newSR = self.getNewSR(player)
            if newSR == -98:
                print('getAllPlayers(): no records for ' + player.getBattletag() + '. newSR: ' + str(newSR))
                newSR = APIConnection.getUpdatedSR(player)
                player.setNewSR(newSR)
                self.updateNewSR(player)
            else:
                player.setNewSR(newSR)

        return playersList

    def getNewSR(self, player):
        # init
        cursor = self.__db.cursor()

        # get the latest id for a certain btag
        sql = "SELECT newSR FROM records WHERE battletag='{}' ORDER BY id DESC".format(
            player.getBattletag())
        cursor.execute(sql)
        result = cursor.fetchone()
        cursor.close()

        if result != None:
            return result[0]
        else:
            print('getNewSR(): No records for ' + player.getBattletag())
            return -98

    def updateNewSR(self, player):
        # init
        cursor = self.__db.cursor()

        # get the latest id
        cursor.execute('SELECT id FROM records ORDER BY id DESC')
        result = cursor.fetchone()

        # insert command
        d = datetime.datetime.now()
        rounded = d - datetime.timedelta(microseconds=d.microsecond)
        sql = "INSERT INTO records (id,battletag,newSR,datetime) VALUES ({}, '{}', '{}', '{}')".format(
            result[0]+1, player.getBattletag(), player.getNewSR(), rounded)
        cursor.execute(sql)
        self.__db.commit()
        cursor.close()

    def getLastUpdateTime(self):
        # init
        cursor = self.__db.cursor()

        # get the latest id for a certain btag
        sql = "SELECT datetime FROM records ORDER BY id DESC"
        cursor.execute(sql)
        result = cursor.fetchone()
        # close
        cursor.close()

        if result != None:

            return datetime.datetime.strptime(result[0], '%Y-%m-%d %H:%M:%S')
        else:
            print('getLastUpdateTime(): cannot get time')
            return datetime.datetime.now()


    def getRecordsByDiscordId(self, discordId):
        # init
        cursor = self.__db.cursor()

        #get battletag from players table
        sql = "SELECT battletag FROM players WHERE discordId='{}'".format(discordId)
        cursor.execute(sql)
        result = cursor.fetchone()

        #check if battletag is found
        if result == None:
            print('getRecordsByDiscordId(): cannot find battletag for discordId ' + discordId)
            return (None, [])
        else:
             records = self.getRecordsByBattleTag(result[0])
             if records != None:
                 return (result[0], records)
             else:
                 print('getRecordsByDiscordId(): cannot find records for discordId ' + discordId)
                 return (None, [])

    def getRecordsByBattleTag(self, battletag):
        # init
        cursor = self.__db.cursor()

        # get the latest id for a certain btag
        sql = "SELECT newSR, datetime FROM records WHERE battletag='{}'".format(battletag)
        cursor.execute(sql)
        result = cursor.fetchall()

        # close
        cursor.close()

        if result == None:
            return None
        else:
            return result

    def updateBattletag(self, oldBattletag, newBattletag):
        # init
        cursor = self.__db.cursor()

        # update players table
        sql = "UPDATE players SET battletag = '{}' WHERE battletag = '{}'".format(newBattletag, oldBattletag)
        cursor.execute(sql)

        #update records table
        sql = "UPDATE records SET battletag = '{}' WHERE battletag = '{}'".format(newBattletag, oldBattletag)
        cursor.execute(sql)

        # commit
        self.__db.commit()

        # close
        cursor.close()


"""Driver testing code
connection = DatabaseConnection()
connection.updateBattletag('MuckyPoo#11217', 'Dëgeneracy#1152')"""