· 10 years ago · Mar 09, 2016, 12:24 PM
1# Importing modules
2import sqlite3
3
4# Constants
5DB_ROOT = "D:\\Sixth Form A2\\OCR Computing A2\\F454 - Computing Coursework\\game_data_db"
6
7# *** *** DATABASE *** *** #
8
9
10# Creates database/table
11def init_db():
12 """
13 Runs on startup of main
14 program, creates table.
15 """
16 open_db()
17 command = ("""
18 CREATE TABLE IF NOT EXISTS players
19 (username TEXT PRIMARY KEY,
20 password TEXT,
21 score INTEGER,
22 kills INTEGER,
23 time INTEGER)
24 """)
25 cur.execute(command)
26 close_db()
27
28
29# Connects to database
30def open_db():
31 """
32 Makes the database and cursor
33 global, connects to database.
34 """
35 global game_data_db
36 game_data_db = sqlite3.connect(DB_ROOT)
37 global cur
38 cur = game_data_db.cursor()
39
40
41# Closes database
42def close_db():
43 """
44 Commits any changes to database and
45 closes it.
46 """
47 game_data_db.commit()
48 game_data_db.close()
49
50
51# Clears database
52def clear_db():
53 """
54 Clears all data from inside
55 players table.
56 """
57 open_db()
58 command = ("""
59 DELETE FROM players
60 """)
61 cur.execute(command)
62 close_db()
63
64
65# Displays all data in database
66def display_db():
67 """
68 Prints to the shell all
69 data contained in database.
70 """
71 open_db()
72 command = ("""
73 SELECT * FROM players
74 """)
75 cur.execute(command)
76 for row in cur:
77 print(row)
78 close_db()
79
80
81# Orders database by high score
82def order_db():
83 """
84 Orders the database rows,
85 so first row has highest
86 score.
87 """
88 open_db()
89 command = ("""
90 SELECT * FROM players
91 ORDER BY score ASC
92 """)
93 cur.execute(command)
94 close_db()
95
96
97# Adds new player to database
98def update_new_player(username, password):
99 """
100 Takes username and password as input,
101 creates new record with blank score,
102 then saves to database.
103 """
104 open_db()
105 param = [(username, password, None, None, None)]
106 command = ("""
107 INSERT INTO players
108 VALUES (?, ?, ?, ?, ?)
109 """)
110 cur.executemany(command, param)
111 close_db()
112
113
114# Deletes player from database
115def delete_player(username):
116 """
117 Deletes the record for a player
118 who's name matches the username
119 input.
120 """
121 open_db()
122 param = [username]
123 command = ("""
124 DELETE FROM players
125 WHERE username = ?
126 """)
127 cur.execute(command, param)
128 close_db()
129
130
131# Updates score attribute
132def update_player_score(username, score):
133 """
134 Takes username and a new score as
135 input, checks to see if new score
136 is larger than current score stored,
137 if so updates database record to
138 highest score.
139 """
140 open_db()
141 result = retrieve_player_score(username)
142 if result is None or result < score:
143 param = [score, username]
144 command = ("""
145 UPDATE players
146 SET score = ?
147 WHERE username = ?
148 """)
149 cur.execute(command, param)
150 close_db()
151
152
153# Retrieves the score for a player
154def retrieve_player_score(username):
155 """
156 Takes username as input.
157 Retrieves score from record
158 where usename matches input.
159 """
160 open_db()
161 param = [username]
162 command = ("""
163 SELECT score FROM players
164 WHERE username = ?
165 """)
166 result = cur.execute(command, param).fetchone()
167 if result is not None:
168 return result[0]
169 close_db()
170
171
172# Updates kills attribute
173def update_player_kills(username, kills):
174 """
175 Takes username and a new kill count as
176 input, checks to see if new count
177 is larger than current kills stored,
178 if so updates database record to
179 highest kill count.
180 """
181 open_db()
182 result = retrieve_player_kills(username)
183 if result is None or result < kills:
184 param = [kills, username]
185 command = ("""
186 UPDATE players
187 SET kills = ?
188 WHERE username = ?
189 """)
190 cur.execute(command, param)
191 close_db()
192
193# Retrieves the highest kills for a player
194def retrieve_player_kills(username):
195 """
196 Takes username as input.
197 Retrieves kills from record
198 where usename matches input.
199 """
200 open_db()
201 param = [username]
202 command = ("""
203 SELECT kills FROM players
204 WHERE username = ?
205 """)
206 result = cur.execute(command, param).fetchone()
207 if result is not None:
208 return result[0]
209 close_db()
210
211
212# Updates time attribute
213def update_player_time(username, time):
214 """
215 Takes username and a new time as
216 input, checks to see if new time
217 is larger than current time stored,
218 if so updates database record to
219 largest time.
220 """
221 open_db()
222 result = retrieve_player_time(username)
223 if result is None or result < time:
224 param = [time, username]
225 command = ("""
226 UPDATE players
227 SET time = ?
228 WHERE username = ?
229 """)
230 cur.execute(command, param)
231 close_db()
232
233
234# Retrieves the highest time for a player
235def retrieve_player_time(username):
236 """
237 Takes username as input.
238 Retrieves time from record
239 where usename matches input.
240 """
241 open_db()
242 param = [username]
243 command = ("""
244 SELECT time FROM players
245 WHERE username = ?
246 """)
247 result = cur.execute(command, param).fetchone()
248 if result is not None:
249 return result[0]
250 close_db()
251
252
253# Retrieves the password for a player
254def retrieve_player_pass(username):
255 """
256 Takes username as input.
257 Retrieves password from record
258 where username matches input.
259 """
260 open_db()
261 param = [username]
262 command = ("""
263 SELECT password FROM players
264 WHERE username = ?
265 """)
266 result = cur.execute(command, param).fetchone()
267 if result is not None:
268 return result[0]
269 close_db()
270
271
272# Retrieves a username from the database
273def retrieve_player_name(username):
274 """
275 Takes username as input.
276 Retrieves username from record
277 where usernames match.
278 """
279 open_db()
280 param = [username]
281 command = ("""
282 SELECT username FROM players
283 WHERE username = ?
284 """)
285 result = cur.execute(command, param).fetchone()
286 if result is not None:
287 return result[0]
288 close_db()
289
290
291# *** *** VALIDATION *** *** #
292
293
294# Checks if name and password match up, logs in if so
295def validate_player_login(username, password):
296 """
297 Takes username and password.
298 Checks database for a record where
299 username and password match input,
300 and returns True or False.
301 """
302 if username == retrieve_player_name(username):
303 if password == retrieve_player_pass(username):
304 return True
305 else:
306 return False
307 else:
308 return False
309
310
311# Checks if name and password for new user are valid
312def validate_player_details(username, password):
313 """
314 Takes username and password as input.
315 Checks length and content for
316 validity.
317 """
318 username = str(username)
319 if len(username) is not 3:
320 return False