File: dbconn.py

package info (click to toggle)
pysiogame 4.20.01-3
  • links: PTS, VCS
  • area: main
  • in suites: forky, sid, trixie
  • size: 27,876 kB
  • sloc: python: 48,742; xml: 3,813; sh: 30; makefile: 11
file content (684 lines) | stat: -rw-r--r-- 29,655 bytes parent folder | download | duplicates (2)
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
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
# -*- coding: utf-8 -*-

# standard lib modules
import datetime
import hashlib
import os
import sys
import sqlite3
import json

import random


class DBConnection():
    def __init__(self, dbname, mainloop):
        self.dbname = dbname
        self.mainloop = mainloop
        self.userid = 1
        self.username = ""
        db_version = 2
        self.connect()
        if self.db_connected:
            #self.db_fix()
            self.c.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'admin'")
            self.conn.commit()
            count = self.c.fetchone()
            if count[0] == 0:
                print("Thank you for downloading eduActiv8.\nCreating database for storage of game data.")
                default_lang = "en_GB"
                os_lang = os.environ.get('LANG', '').split('.')
                if os_lang[0] in self.mainloop.config.all_lng:
                    default_lang = os_lang[0][:]
                else:
                    lcount = len(self.mainloop.config.all_lng)
                    for i in range(lcount):
                        if os_lang[0][0:2] == self.mainloop.config.all_lng[i][0:2]:
                            default_lang = self.mainloop.config.all_lng[i]
                            continue

                self.c.execute(
                    "CREATE TABLE users (username TEXT, password TEXT, date_added TEXT, last_login TEXT, lang TEXT, sounds INTEGER, espeak INTEGER, screenw INTEGER, screenh INTEGER, score INTEGER, scheme INTEGER, age_group INTEGER, colors TEXT)")
                self.c.execute("CREATE TABLE levelcursors (userid INTEGER KEY, gameid INTEGER KEY,lastlvl INTEGER)")
                # self.c.execute("CREATE TABLE completions (userid integer, constructor text, variant integer, lvl_completed integer)")
                self.c.execute(
                    "CREATE TABLE completions (userid INTEGER KEY, gameid INTEGER KEY, lvl_completed INTEGER, lang_id INTEGER, num_completed INTEGER, age INTEGER)")
                # admin data - 1, admin, admin_pass, "en_gb", "00000"
                self.c.execute(
                    "CREATE TABLE admin (admin_id INTEGER KEY, admin_name TEXT, admin_pass TEXT, default_lang TEXT, login_screen_defaults TEXT, autologin_userid INTEGER, autologin INTEGER, db_version INTEGER)")
                # self.c.execute("INSERT INTO admin VALUES (?, ?, ?, ?, ?)", (admin_id, admin_name, admin_pass, default_lang, login_screen_defaults, db_version,autologin_userid TEXT,autologin INTEGER))
                self.c.execute("INSERT INTO admin VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                               (0, "", "", default_lang, "01101", 0, 0, db_version))
                self.conn.commit()

                self.lang = self.mainloop.lang
                self.lang.load_language(lang_code=default_lang)
                guest_name = self.lang.b["Guest"]
                if sys.version_info < (3, 0):
                    try:
                        if not isinstance(guest_name, unicode):
                            guest_name = unicode(guest_name, "utf-8")
                    except:
                        pass

                self.add_user(guest_name, "", default_lang, 0, 0, 800, 480)
                print("Database successfully created.")
            else:
                # check the db_version for future updates if database needs to be changed this will be used
                # to upgrade the db instead of recreating the db from scratch

                self.c.execute("SELECT db_version FROM admin WHERE (admin_id = 0)")
                self.conn.commit()
                row = self.c.fetchone()
                current_db_ver = row[0]
                if 0 < current_db_ver < 2:
                    print("Thanks for downloading the latest version of the game.\nThe database structure has changed in this version.\nUpdating the database to version 2...")

                if current_db_ver == 1:
                    #print("ALTER TABLE users ADD COLUMN colors TEXT")
                    self.c.execute("ALTER TABLE users ADD COLUMN colors TEXT")
                    self.c.execute("UPDATE admin SET db_version = ? WHERE (admin_id = 0)", (db_version,))
                    self.conn.commit()

                    print("Database updated.")

                """
                row = self.c.fetchone()
                current_db_ver = row[0]
                if 0 < current_db_ver < 4:
                    print("Database structure changed in this version of the game. Updating the database to version 4.")

                if current_db_ver == 1:
                    self.c.execute("ALTER TABLE admin ADD COLUMN autologin_userid INTEGER DEFAULT 0")
                    self.c.execute("ALTER TABLE admin ADD COLUMN autologin INTEGER DEFAULT 0")
                    self.c.execute("ALTER TABLE completions ADD COLUMN lang_id INTEGER DEFAULT 1")
                    self.c.execute("UPDATE admin SET db_version = ? WHERE (admin_id = 0)", (db_version,))

                    self.c.execute("ALTER TABLE users ADD COLUMN scheme INTEGER DEFAULT 0")
                    self.c.execute("UPDATE users SET scheme = 0")
                    self.conn.commit()
                    # update db_version
                elif current_db_ver == 2:
                    self.c.execute("ALTER TABLE users ADD COLUMN scheme INTEGER DEFAULT 0")
                    self.c.execute("UPDATE users SET scheme = 0")
                    self.c.execute("UPDATE admin SET db_version = ? WHERE (admin_id = 0)", (db_version,))
                    self.conn.commit()
                elif current_db_ver == 3:
                    self.c.execute("ALTER TABLE completions ADD COLUMN lang_id INTEGER DEFAULT 1")
                    self.c.execute("UPDATE admin SET db_version = ? WHERE (admin_id = 0)", (db_version,))
                    self.conn.commit()
                """

                """
                    #this was already commented out
                    self.c.execute("UPDATE users SET username = ? WHERE (ROWID=?)", ("Guest", 1))
                    self.c.execute("SELECT username FROM users WHERE (ROWID=?)", (1,))
                    self.conn.commit()
                    row = self.c.fetchone()
                    name = row[0]
                    print("Guest username set to: %s" % name)
                """
                """
                if 0 < current_db_ver < 4:
                    print("Database version updated from %d to %d." % (current_db_ver, db_version))
                """

    def unset_autologin(self):
        if self.db_connected:
            self.c.execute("UPDATE admin SET autologin_userid = 0, autologin = 0 WHERE (admin_id = 0)")
            self.conn.commit()

    def set_autologin(self, userid):
        if self.db_connected:
            self.c.execute("UPDATE admin SET autologin_userid = ?, autologin = 1 WHERE (admin_id = 0)", (userid,))
            self.conn.commit()

    def get_autologin(self):
        if self.db_connected:
            self.c.execute("SELECT autologin_userid, autologin FROM admin WHERE (admin_id = 0)")
            self.conn.commit()
            row = self.c.fetchone()
            if row[1] == 1:
                self.userid = row[0]
                return row
            else:
                return None

    def admin_exists(self):
        if self.db_connected:
            self.c.execute("SELECT admin_name, admin_pass FROM admin WHERE (admin_id = 0)")
            self.conn.commit()
            row = self.c.fetchone()
            if row[0] == "" and row[1] == "":
                return False
            else:
                return True

    def get_login_defs(self):
        if self.db_connected:
            self.c.execute("SELECT default_lang, login_screen_defaults FROM admin WHERE (admin_id = 0)")
            self.conn.commit()
            row = self.c.fetchone()
            return row

    def get_lang(self):
        if self.db_connected:
            self.c.execute("SELECT default_lang FROM admin WHERE (admin_id = 0)")
            self.conn.commit()
            row = self.c.fetchone()
            return row[0]

    def set_lang(self, lang):
        # default_lang
        if self.db_connected:
            self.c.execute("UPDATE admin SET default_lang = ? WHERE (admin_id = 0)", (lang,))
            self.conn.commit()

    def add_admin_name(self, admin_name, password):
        if self.db_connected:
            self.c.execute("SELECT admin_name, admin_pass FROM admin WHERE (admin_id = 0)")
            self.conn.commit()
            row = self.c.fetchone()
            if row[0] == "" and row[1] == "":
                m = hashlib.md5()
                m.update(password.encode("utf-8"))
                md5_password = m.hexdigest()
                self.c.execute("UPDATE admin SET admin_name = ?, admin_pass = ? WHERE (admin_id = 0)",
                               (admin_name, md5_password))
                self.conn.commit()
                return 0  # "Admin's password has been updated"
            else:
                return -1  # "ERROR: This operation is not allowed at this point"
        return ""

    def update_admin_password(self, prev_pass, new_pass):
        if self.db_connected:
            m = hashlib.md5()
            m.update(prev_pass.encode("utf-8"))
            md5prev_password = m.hexdigest()
            self.c.execute("SELECT admin_name FROM admin WHERE (admin_pass = ?)", (md5prev_password,))
            self.conn.commit()
            count = self.c.fetchone()
            if count is None:
                return -1  # "Previous password doesn't seem to be in the database"
            else:
                m2 = hashlib.md5()
                m2.update(new_pass.encode("utf-8"))
                md5new_password = m2.hexdigest()
                self.c.execute("UPDATE admin SET admin_pass = ? WHERE (admin_pass = ?)",
                               (md5new_password, md5prev_password))
                self.conn.commit()
                return 0  # "Admin's password has been updated"
        return ""

    """
    self.c.execute("CREATE TABLE admin (admin_id INTEGER KEY, admin_name TEXT, admin_pass TEXT, default_lang TEXT, login_screen_defaults TEXT)")
    #self.c.execute("INSERT INTO admin VALUES (?, ?, ?, ?, ?)", (admin_id, admin_name, admin_pass, default_lang, login_screen_defaults))
    self.c.execute("INSERT INTO admin VALUES (?, ?, ?, ?, ?)", (0, "", "", "en_gb", "01011"))
    """

    def update_defaults(self, defs):
        if self.db_connected:
            self.c.execute("UPDATE admin SET login_screen_defaults = ? WHERE (admin_id = 0)", (defs,))
            self.conn.commit()

    def get_now(self):
        return str(datetime.datetime.now())[:19]

    def connect(self):
        try:
            self.conn = sqlite3.connect(self.dbname)
            self.c = self.conn.cursor()
            self.db_connected = True
        except:
            self.db_connected = False

    def update_cursor(self, userid, gameid, lastlvl):
        if self.db_connected:
            self.c.execute("SELECT count(*) FROM levelcursors WHERE (userid = ? AND gameid = ?)", (userid, gameid))
            self.conn.commit()
            count = self.c.fetchone()
            if count[0] == 0:
                self.c.execute("INSERT INTO levelcursors VALUES (?, ?, ?)", (userid, gameid, lastlvl))
            else:
                self.c.execute("UPDATE levelcursors SET lastlvl = ?  WHERE (userid=? AND gameid = ?)",
                               (lastlvl, userid, gameid))
            self.conn.commit()

    def get_age(self):
        if self.mainloop.config.user_age_group < 7:
            return self.mainloop.config.user_age_group
        else:
            return self.mainloop.config.max_age

    def get_lang_id(self):
        if self.mainloop.m is not None:
            if self.mainloop.m.lang_activity:
                return self.mainloop.lang.lang_id
            else:
                return 0
        else:
            return self.mainloop.lang.lang_id

    def db_fix(self):
        pass
        #self.c.execute("SELECT num_completed FROM completions WHERE (gameid = ?)", (26,))
        #self.conn.commit()
        #count = self.c.fetchone()
        #self.c.execute("DELETE FROM completions WHERE (gameid = ?)", (26,))
        #self.conn.commit()

    def update_completion(self, userid, gameid, lvl):
        if self.db_connected:
            age = self.get_age()
            lng = self.get_lang_id()
            self.c.execute(
                "SELECT num_completed FROM completions WHERE (userid = ? AND gameid = ? AND lvl_completed = ? AND lang_id = ? AND age = ?)",
                (userid, gameid, lvl, lng, age))
            self.conn.commit()
            count = self.c.fetchone()

            if count is None:
                self.c.execute("INSERT INTO completions VALUES (?, ?, ?, ?, ?, ?)",
                               (userid, gameid, lvl, lng, 1, age))
            else:
                self.c.execute(
                    "UPDATE completions SET num_completed = ?  WHERE (userid = ? AND gameid = ? AND lang_id = ? AND lvl_completed = ? AND age = ?)",
                    (count[0] + 1, userid, gameid, lng, lvl, age))
            self.conn.commit()

    def update_completion_populate_db_heavy_stress_test(self):
        """Used for testing only.
        Adds completion records across 250 games with 10 levels completed each in 4 languages for all age groups
        for 100 users making the database take over 130MB"""
        if self.db_connected:
            for lvl in range(1, 11):
                for gameid in range(250):
                    for userid in range(100):
                        for lng in range(4):
                            for age in range(7):
                                self.c.execute("INSERT INTO completions VALUES (?, ?, ?, ?, ?, ?)", (userid, gameid, lvl, lng, random.randint(1, 5), age))
            self.conn.commit()

    def update_completion_populate_db_light_stress_test(self):
        """Used for testing only. 30 kids across 7 classes - speaking 2 languages."""
        if self.db_connected:
            for lvl in range(1, 6):
                for gameid in range(250):
                    for userid in range(30):
                        for lng in range(2):
                            for age in range(0, 7):
                                self.c.execute("INSERT INTO completions VALUES (?, ?, ?, ?, ?, ?)", (userid, gameid, lvl, lng, random.randint(1, 5), age))
            self.conn.commit()

    def update_completion_populate_db_test(self):
        """Used for testing only. Each year go one level higher."""
        if self.db_connected:
            for age in range(0, 7):
                for lvl in range(1, 9):
                    if lvl < age + 2:
                        for gameid in range(270):
                            for userid in range(3):
                                for lng in range(3):
                                    self.c.execute("INSERT INTO completions VALUES (?, ?, ?, ?, ?, ?)", (userid, gameid, lvl, lng, random.randint(1, 5), age))
            self.conn.commit()

    def query_completion(self, userid, gameid, lvl, lang_activ=False):
        if self.db_connected:
            age = self.get_age()
            if not lang_activ:
                lng = self.get_lang_id()
            else:
                lng = self.mainloop.lang.lang_id

            self.c.execute(
                "SELECT num_completed FROM completions WHERE (userid = ? AND gameid = ? AND lang_id = ? AND lvl_completed = ? AND age = ?)",
                (userid, gameid, lng, lvl, age))

            self.conn.commit()
            count = self.c.fetchone()
            if count is None:
                return 0
            else:
                return count[0]

    def query_completion_all_levels(self, userid, gameid, lang_activ=False):
        if self.db_connected:
            age = self.get_age()
            if not lang_activ:
                lng = self.get_lang_id()
            else:
                lng = self.mainloop.lang.lang_id

            self.c.execute(
                "SELECT * FROM completions WHERE (userid = ? AND gameid = ? AND lang_id = ? AND age = ?)",
                (userid, gameid, lng, age))

            self.conn.commit()
            #count = self.c.fetchone()
            all = self.c.fetchall()
            return all

    def query_completion_all_ages(self, userid, gameid, lang_activ=False):
        if self.db_connected:
            if not lang_activ:
                lng = self.get_lang_id()
            else:
                lng = self.mainloop.lang.lang_id

            self.c.execute(
                "SELECT * FROM completions WHERE (userid = ? AND gameid = ? AND lang_id = ?)",
                (userid, gameid, lng))

            self.conn.commit()
            #count = self.c.fetchone()
            all = self.c.fetchall()
            return all

    def get_completion_count(self, userid):
        if self.db_connected:
            self.c.execute("SELECT count(*) FROM completions WHERE userid=?", (userid,))
            self.conn.commit()
            count = self.c.fetchone()
            if count is None:
                return 0
            else:
                return count[0]

    def completion_book(self, userid, offset=0):
        if self.db_connected:
            self.c.execute(
                "SELECT gameid, lvl_completed, lang_id, num_completed, age FROM completions WHERE (userid = ?) LIMIT 10 OFFSET ?",
                (userid, offset))
            self.conn.commit()
            temp = []
            for each in self.c:
                temp.append(each)
            return temp

    def load_all_cursors(self, userid):
        if self.db_connected:
            self.c.execute("SELECT * FROM levelcursors WHERE (userid = ?)", (userid,))
            self.conn.commit()
            temp = dict()
            for each in self.c:
                temp[each[1]] = each[2]
            return temp

    def load_usernames(self):
        if self.db_connected:
            self.c.execute("SELECT username FROM users")
            self.conn.commit()
            temp = []
            for each in self.c:
                temp.append(each[0])
            return temp

    def get_user_id(self, username):
        if self.db_connected:
            self.c.execute("SELECT ROWID FROM users WHERE username = ?", (username,))
            self.conn.commit()
            row = self.c.fetchone()
            if row is not None:
                return row[0]
        return None

    def get_user_score(self, userid):
        if self.db_connected:
            self.c.execute("SELECT score FROM users WHERE ROWID = ?", (userid,))
            self.conn.commit()
            row = self.c.fetchone()
            if row is not None:
                return row[0]
        return None

    def increase_score(self, userid, points):
        if self.db_connected:
            prev_score = self.get_user_score(userid)
            if prev_score is not None:
                if points > 0:
                    new_score = prev_score + points
                    self.c.execute("UPDATE users SET score = ? WHERE (ROWID=?)", (new_score, userid))
                    self.conn.commit()
                    return new_score
                else:
                    return prev_score
        return None

    def change_username(self, prev_name, new_name):
        if self.db_connected:
            uid = self.get_user_id(prev_name)
            # check if new username is not taken
            uid_new = self.get_user_id(new_name)
            if uid_new is None and uid is not None:
                self.c.execute("UPDATE users SET username = ? WHERE (ROWID=?)", (new_name, uid))
                self.conn.commit()

    def load_user_details(self, username):
        if self.db_connected:
            self.c.execute("SELECT username, date_added, last_login, score FROM users WHERE username = ?", (username,))
            self.conn.commit()
            count = self.c.fetchone()
            return count

    def add_user(self, username, password, lang, sounds, espeak, screenw, screenh):
        if self.db_connected:
            self.c.execute("SELECT count(*) FROM users WHERE username=?", (username,))
            self.conn.commit()
            count = self.c.fetchone()
            m = hashlib.md5()
            m.update(password.encode("utf-8"))
            md5password = m.hexdigest()
            if count[0] == 0:
                self.c.execute("INSERT INTO users VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (
                username, md5password, self.get_now(), "", lang, sounds, espeak, screenw, screenh, 0, 0, 0, ""))
                self.conn.commit()
                return 0  # "%s added" % username
            else:
                return -1  # "This user name already exists, please choose a different one"
        return ""

    def del_user(self, username):
        # check if user exists + get user id
        if self.db_connected:
            self.c.execute("SELECT count(*) FROM users")
            self.conn.commit()
            count = self.c.fetchall()
            if count is not None and count[0][0] > 1:
                self.c.execute("SELECT ROWID FROM users WHERE username=?", (username,))
                self.conn.commit()
                row = self.c.fetchone()
                if row is not None:
                    userid = row[0]
                    self.c.execute("DELETE FROM levelcursors WHERE userid = ?", (userid,))
                    self.c.execute("DELETE FROM completions WHERE userid = ?", (userid,))
                    self.c.execute("DELETE FROM users WHERE username = ?", (username,))
                    self.conn.commit()
                    return 0  # "%s deleted from database." % username
        return -1

    def save_user_settings(self, lang, sounds, espeak, screenw, screenh, scheme):
        if self.db_connected:
            self.c.execute(
                "UPDATE users SET lang = ?, sounds = ?, espeak = ?, screenw = ?, screenh = ?, scheme = ? WHERE (ROWID=?)",
                (lang, sounds, espeak, screenw, screenh, scheme, self.userid))
            self.conn.commit()

    def save_user_lang(self, lang):
        if self.db_connected:
            self.c.execute("UPDATE users SET lang = ? WHERE (ROWID=?)", (lang, self.userid))
            self.conn.commit()

    def save_user_colors(self):
        if self.db_connected:
            self.c.execute("UPDATE users SET colors = ? WHERE (ROWID=?)", (json.dumps(self.mainloop.cl.color_sliders),
                                                                           self.userid))
            self.conn.commit()

    def load_user_colors(self):
        if self.db_connected:
            self.c.execute("SELECT colors FROM users WHERE (ROWID=?)", (self.userid,))
            self.conn.commit()
            row = self.c.fetchone()
            if row[0] is not None and len(row[0]) > 0:
                self.mainloop.cl.load_colors(json.loads(row[0]))
            else:
                self.mainloop.cl.reset_colors()
                self.mainloop.cl.create_colors()

    def load_user_settings(self, userid):
        if self.db_connected:
            self.load_user_colors()
            self.c.execute("SELECT lang, sounds, espeak, screenw, screenh, scheme FROM users WHERE (ROWID=?)",
                           (self.userid,))
            self.conn.commit()
            row = self.c.fetchone()
            return row

    def update_age_group(self, username, age_group):
        if self.db_connected:
            # print("updating age_group for user %s to %d" % (username, age_group))
            self.c.execute("UPDATE users SET age_group = ? WHERE (username=?)", (age_group, username))
            self.conn.commit()

    def get_age_group(self, username="", userid=-1):
        if self.db_connected:
            if username != "":
                self.c.execute("SELECT age_group FROM users WHERE (username=?)", (username,))
            else:
                self.c.execute("SELECT age_group FROM users WHERE (ROWID=?)", (userid,))
            self.conn.commit()

            row = self.c.fetchone()
            if row is None:
                return None
            else:
                return row[0]

    def update_user(self, prev_username, prev_password, new_username, new_password):
        if self.db_connected:
            m = hashlib.md5()
            m.update(prev_password.encode("utf-8"))
            md5prev_password = m.hexdigest()
            self.c.execute("SELECT count(*) FROM users WHERE (username=? AND password=?)",
                           (prev_username, md5prev_password))
            self.conn.commit()
            count = self.c.fetchone()
            if count[0] == 0:
                return -2  # "Nothing to update..."
            else:
                self.c.execute("SELECT count(*) FROM users WHERE username=?", (new_username,))
                self.conn.commit()
                count = self.c.fetchone()
                if count[0] == 0:
                    m = hashlib.md5()
                    m.update(new_password.encode("utf-8"))
                    md5new_password = m.hexdigest()
                    self.c.execute("UPDATE users SET username = ? , password = ? WHERE (username=? AND password=?)",
                                   (new_username, md5new_password, prev_username, md5prev_password))
                    self.conn.commit()
                    if prev_username != new_username:
                        return 0  # "%s, your name was updated to %s" % (prev_username, new_username)
                    if prev_password != new_password:
                        return 1  # "%s, Your password has been updated" % new_username
                else:
                    return -1  # "This username already exists, please choose a different one"

        return ""

    def login_user(self, username, password):
        if self.db_connected:
            m = hashlib.md5()
            m.update(password.encode("utf-8"))
            md5password = m.hexdigest()
            self.c.execute("SELECT ROWID, username FROM users WHERE username=? AND password=?", (username, md5password))
            self.conn.commit()
            a = self.c.fetchone()
            if a is not None:
                self.userid = a[0]
                self.username = a[1]
                self.c.execute("UPDATE users SET last_login = ? WHERE (ROWID=?)", (self.get_now(), self.userid))
                self.conn.commit()
                return 0  # "Hello %s! You are logged in." % a[1] #(a[1],a[0])
            else:
                self.userid = -1
                return -1  # "This username and password combination doesn't exist."
        return ""

    def login_auto(self, userid):
        if self.db_connected:
            self.c.execute("SELECT ROWID, username FROM users WHERE ROWID=?", (userid,))
            self.conn.commit()
            a = self.c.fetchone()
            if a is not None:
                self.userid = a[0]
                self.username = a[1]
                self.c.execute("UPDATE users SET last_login = ? WHERE (ROWID=?)", (self.get_now(), self.userid))
                self.conn.commit()
                return 0  # "Hello %s! You are logged in." % a[1] #(a[1],a[0])
            else:
                self.userid = -1
                return -1  # "This username doesn't exist."
        return ""

    def login_user_no_pass(self, username):
        if self.db_connected:
            self.c.execute("SELECT ROWID, username FROM users WHERE username=?", (username,))
            self.conn.commit()
            a = self.c.fetchone()
            if a is not None:
                self.userid = a[0]
                self.username = a[1]
                self.c.execute("UPDATE users SET last_login = ? WHERE (ROWID=?)", (self.get_now(), self.userid))
                self.conn.commit()
                return 0  # "Hello %s! You are logged in." % a[1] #(a[1],a[0])
            else:
                self.userid = -1
                return -1  # "This username doesn't exist."
        return ""

    def login_admin(self, username, password):
        if self.db_connected:
            m = hashlib.md5()
            m.update(password.encode("utf-8"))
            md5password = m.hexdigest()
            self.c.execute("SELECT ROWID, admin_name FROM admin WHERE admin_name=? AND admin_pass=?",
                           (username, md5password))
            self.conn.commit()
            a = self.c.fetchone()
            if a is not None:
                self.userid = -2
                return 0  # "You are logged in."
            else:
                self.userid = -1
                return -1  # "This username and password combination doesn't exist."
        return -2

    def printlvls(self):
        if self.db_connected:
            self.c.execute("SELECT * FROM levelcursors")
            self.conn.commit()
            a = self.c.fetchall()
            for each in a:
                print(each)

    def printcompl(self):
        if self.db_connected:
            self.c.execute("SELECT * FROM completions")
            self.conn.commit()
            a = self.c.fetchall()
            for each in a:
                print(each)

    def print_db(self):
        if self.db_connected:
            self.c.execute("SELECT ROWID, username, password FROM users")
            self.conn.commit()
            a = self.c.fetchall()
            for each in a:
                print(each)

    def close(self):
        if self.db_connected:
            self.conn.close()
            self.db_connected = False