· 9 years ago · Jul 04, 2017, 08:36 AM
1import MySQLdb
2
3SQL_HOST = ""
4SQL_USER = ""
5SQL_PSWD = ""
6SQL_DTBS = ""
7
8class DatabaseManager(object):
9 """
10 This class manages all database connections. The main idea of this object
11 is to create an easy interface so we can easilly and safely query values
12 from a database witout worrying about convulent and complex sqlite syntax.
13
14 This interface allows us to safely call and remove objects from the database
15 so that users do not have to access the database directly, but through
16 a simple API structure.
17
18 We should only have one database instance so it is unnecesarry to create
19 another object to hold the instances of this object.
20 """
21 players = []
22 def __init__(self, host, user, password, database):
23 """
24 Default Constructor
25
26 Initialize all the default values and open a connection with the SQLite
27 database. Create the tables if they don't exist.
28
29 @PARAM host - Host of the database
30 @PARAM user - User of the database
31 @PARAM password - Password of the database
32 @PARAM database - Database to connect in
33 """
34 self.host = host
35 self.user = user
36 self.password = password
37 self.database = database
38 self.connection = MySQLdb.connect(self.host, self.user, self.password, self.database)
39 self.connection.text_factory = str
40 self.cursor = self.connection.cursor()
41
42 def __del__(self):
43 """
44 Default deconstructor.
45
46 Executed when a database instance is destroyed. Ensure that the database
47 is saved and closed.
48 """
49 self.close()
50
51 def __contains__(self, key):
52 """
53 Executed automatically when we attempt to test to see if an element exists
54 within the database.
55
56 @PARAM key - The value to test for validity
57 @RETURN boolean - Whether or not the value exists
58 """
59 key = str(key)
60 self.execute("SELECT games FROM Player WHERE name=?", key)
61 result = self.cursor.fetchone()
62 if bool(result):
63 return True
64 return False
65
66 def __iter__(self):
67 """
68 Executed automatically when we attempt to iterate through an instance
69 of this class. Query all the names from the playerstats table and
70 yield each name as a seperate object.
71
72 @RETURN yield object - string objects which represent player's names
73 """
74 self.execute("SELECT * FROM Player")
75 self.cursor.fetchall().itervalues()
76
77 def execute(self, parseString, *args):
78 """
79 A wrapper function to simulate the execute() method of a cursor object.
80
81 @PARAM parseString - the string query line of a SQLite statement
82 """
83 self.cursor.execute(parseString, args)
84
85 def getPlayerInfo(self, name, infoType):
86 """
87 Returns a players attribute from the playerstats table.
88
89 @PARAM name - the name of the player your wish to check
90 @PARAM infoType - the column name of the value you wish to return
91 @RETURN object - returns an object type of the value to which statType returns
92 """
93 infoType = str(infoType).replace("'", "''")
94 if hasattr(infoType, "__len__"):
95 query = "SELECT " + ",".join(map(str, infoType)) + " FROM Player WHERE name=?"
96 else:
97 query = "SELECT " + str(infoType) + " FROM Player WHERE name=?"
98 self.execute(query, name)
99 return self.fetchone()
100
101 def query(self, table, primaryKeyName, primaryKeyValue, options):
102 """
103 Queries results from the table for one person and returns either a
104 tuple or a single value depending on the amount of values passed.
105
106 @PARAM table - the table of which this query should take place
107 @PARAM primaryKeyName - the name of the key you wish to test for equality
108 @PARAM primaryKeyValue - the options to update where the primaryKeyName's value equates to this value
109 @PARAM options - either a single value or a tuple which we will get the values of
110 """
111 if hasattr(options, "__len__"):
112 query = "SELECT " + ",".join(map(lambda x: str(x).replace("'", "''"), options)) + " FROM " + table \
113 + " WHERE " + primaryKeyName + "='" + primaryKeyValue + "'"
114 else:
115 query = "SELECT " + str(options).replace("'", "''") + " FROM " + table + \
116 " WHERE " + primaryKeyName + "='" + primaryKeyValue + "'"
117 self.execute(query)
118 return self.fetchone()
119
120 def fetchall(self):
121 """
122 Mimics the sqlite fetchall method which recides within a cursor object.
123 Ensures that the true values are added to a list so that we don't need
124 to index it if the value is only one item in length (e.g. item instead
125 of (item,)...)
126
127 @RETURN list - attributes from which the query returned
128 """
129 trueValues = []
130 for value in self.cursor.fetchall():
131 if isinstance(value, tuple):
132 if len(value) > 1:
133 tempValues = []
134 for tempValue in value:
135 if isinstance(tempValue, long):
136 tempValue = int(tempValue)
137 tempValues.append(tempValue)
138 trueValues.append(tempValues)
139 else:
140 if isinstance(value[0], long):
141 trueValues.append(int(value[0]))
142 else:
143 trueValues.append(value[0])
144 else:
145 if isinstance(value, long):
146 value = int(value)
147 trueValues.append(value)
148 return trueValues
149
150 def fetchone(self):
151 """
152 Mimics the sqlite fetchone method which recides within a cursor object.
153 Ensures that a single value is returned from the cursor object if only
154 one object exists within the tuple from the query, otherwise it returns
155 the query result
156
157 @RETURN Object - the result from the query command
158 """
159 result = self.cursor.fetchone()
160 if hasattr(result, "__iter__"):
161 if len(result) == 1:
162 trueResults = result[0]
163 if isinstance(trueResults, long):
164 trueResults = int(trueResults)
165 return trueResults
166 else:
167 trueResults = []
168 for trueResult in result:
169 if isinstance(trueResult, long):
170 trueResult = int(trueResult)
171 trueResults.append(trueResult)
172 return trueResults
173 if isinstance(result, long):
174 result = int(result)
175 return result
176
177 def close(self):
178 """
179 Closes the connections so that no further queries can be made.
180 """
181 self.cursor.close()
182 self.connection.close()
183
184class PlayerManager(object):
185 """
186 This class is used to manage players. This class itself simulates a
187 dictionary but gives additional functions which allow us to modify the way
188 the singelton is perceived. This will store individual Player objects by a
189 tag name and allows us to reference the singleton object by dictionary item
190 assignment / retrieval to retrieve the retrespective player object.
191 """
192 def __init__(self):
193 """ Default constructor, initialize variables """
194 self.players = {}
195
196 def __getitem__(self, name):
197 """
198 Executed when object[x] is executed on the singleton. As we wish to
199 simulate dictionary attributes, we want to return the player object
200 referenced by their name
201
202 @PARAM str name - The ID of the user we wish to return the Player object
203 @RETURN Player - The Player object referenced by ID
204 """
205 return self.players[name]
206
207 def __iter__(self):
208 """
209 This function executes automatically when "for x in object" is executed.
210 We want to return all the player objects inside the player dictionary.
211
212 @RETURN generator - A generator to a list of all the player objects
213 """
214 return self.players.itervalues()
215
216 def __contains__(self, name):
217 """
218 Executed automatically when we use the syntax "x in object". We will
219 test if the name in question exists within the players dictionary.
220
221 @PARAM str name - The name to test for validation
222 @RETURN boolean - Whether or not the name already exists
223 """
224 return name in self.players
225
226 def __delitem__(self, name):
227 """
228 Removes the player and object from memory so the RAM is cleared.
229 object.removePlayer(name) == object.__delitem__(name)
230
231 @PARAM str name - The name of the player to remove
232 """
233 self.removePlayer(name)
234
235 def addPlayer(self, name):
236 """
237 Add the player into memory as an Player object
238
239 @PARAM str name - The name of the player to add
240 """
241 self.players[name] = PlayerObject(name)
242
243 def removePlayer(self, name):
244 """
245 Removes the player and object from memory
246
247 @PARAM str name - The name of the player to remove
248 """
249 if name in self.players:
250 del self.players[name]
251
252class PlayerObject(object):
253 """
254 This is an object which will revolve around all active players. This should
255 contain all relevant information about a particular player, and cache all
256 their details so we don't need to query the database at regular intervals.
257 """
258 name = None
259 cache = None
260 def __init__(self, name):
261 """"
262 Default constructor, initialize variables
263 Set up all necessary objects and cache values here.
264
265 @PARAM str name - The name of the user who this object references
266 """
267 self.name = str(name)
268 self.cache = PlayerCache(self.name)
269
270 def __getattr__(self, attribute):
271 """
272 This object will allow us to get attributes from the cache object
273
274 @PARAM str attribute - The attribute of the PlayerCache we want to
275 @RETURN str attribute - The attribute if it he exist
276 """
277 if hasattr(self.cache, attribute):
278 return getattr(self.cache, attribute)
279 raise AttributeError("Player object has no attribute %s" % attribute)
280
281 def __setattr__(self, attribute, value):
282 """
283 This object will allow us to reassing values in the PlayerCache object
284
285 @PARAM str attribute - The attribute to change
286 @PARAM int|str value - The value to set to the attribute
287 @RETURN mixed attribute - setattr object if cache has attribute
288 """
289 if hasattr(self.cache, attribute):
290 return setattr(self.cache, attribute, value)
291 object.__setattr__(attribute, value)
292
293class PlayerCache(object):
294 """
295 This object should be used by each player and should store all relevant
296 information regarding their current stats. Only refresh if explicitly told to;
297 we never need to refresh on ticks. This means we can reduce the amount of
298 DB queries we need which improves read time and efficiency.
299 """
300 def __init__(self, name):
301 """"
302 Default constructor, initialize variables
303
304 @PARAM str name - The name of the player on the DB
305 """
306 self.name = name
307 self.update()
308
309 def update(self):
310 """
311 This method simply refreshes the local attributes
312 with the attributes from the dictionnary.
313 """
314 database.execute("SELECT games, scores, strikes, spares, splits, opens FROM Player WHERE name=?", self.name)
315 self.games, self.scores, self.strikes, self.spares, self.splits, self.opens = database.fetchone()
316 self.minimum, self.maximum, self.average = min(self.scores), max(self.scores), round(sum(map(int, self.scores)) / float(len(self.scores)))
317
318players = PlayerManager()
319database = DatabaseManager(SQL_HOST, SQL_USER, SQL_PSWD, SQL_DTBS)