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