5nYthy5U

1import sqlite3
2import sys, time
3
4def clearScreen():
5    print("\n" * 50)
6    
7    
8def createConnection():
9    ,
10            ('''userID INTEGER NOT NULL,
11            topicID INTEGER NOT NULL,
12            score INTEGER NOT NULL,
13            difficulty VARCHAR(20),
14            grade VARCHAR(5),
15            FOREIGN KEY(userID) REFERENCES users(userID),
16            FOREIGN KEY(topicID) REFERENCES topics(topicID));
17            ''')
18        
19#         cursor.execute('''
20#             SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
21#         ''')
22#         
23#         print(cursor.fetchall())
24        
25        
26    except:
27        print("Couldn't create the tables in {}".format(db_file))
28
29        
30""" create a database connection to the SQLite database
31        specified by db_file
32    :param db_file: database file
33    :return: Connection object or None
34    """
35    try:
36        return sqlite3.connect(db_file)
37    except:
38        return False
39
40
41def init():
42    try:
43        cursor = conn.cursor()
44    
45        cursor.execute('''
46            CREATE TABLE IF NOT EXISTS users(
47            userID INTEGER PRIMARY KEY,
48            username VARCHAR(20) NOT NULL,
49            firstname VARCHAR(20) NOT NULL,
50            surname VARCHAR(20) NOT NULL,
51            age INTEGER(2) NOT NULL,
52            yeargroup VARCHAR(10) NOT NULL,
53            password VARCHAR(20) NOT NULL);
54            ''')
55        
56        cursor.execute('''
57            CREATE TABLE IF NOT EXISTS topics(
58            topicID INTEGER PRIMARY KEY,
59            topicName VARCHAR(30) NOT NULL);
60            ''')
61        
62        cursor.execute('''
63            CREATE TABLE IF NOT EXISTS questions(
64            questionID INTEGER PRIMARY KEY,
65            topicID INTEGER NOT NULL,
66            question VARCHAR(100) NOT NULL,
67            option1 VARCHAR(50),
68            option2 VARCHAR(50),
69            option3 VARCHAR(50),
70            option4 VARCHAR(50),
71            answer VARCHAR(50),
72            FOREIGN KEY(topicID) REFERENCES topics(topicID));
73            ''')
74        
75        cursor.execute('''
76            CREATE TABLE IF NOT EXISTS scores(
77            scoreID INTEGER PRIMARY KEY,
78            userID INTEGER NOT NULL,
79            topicID INTEGER NOT NULL,
80            score INTEGER NOT NULL,
81            difficulty VARCHAR(20),
82            grade VARCHAR(5),
83            FOREIGN KEY(userID) REFERENCES users(userID),
84            FOREIGN KEY(topicID) REFERENCES topics(topicID));
85            ''')
86        
87#         cursor.execute('''
88#             SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
89#         ''')
90#         
91#         print(cursor.fetchall())
92        
93        
94    except:
95        print("Couldn't create the tables in {}".format(db_file))