nphYbJJa

1class DatabaseCreation:
2
3    def __init__(self, filename, folder_path):
4
5        self.filename = filename
6        self.folder_path = folder_path
7        self.curr_dir = os.path.dirname(os.path.realpath(__file__))
8
9        if folder_path is None:
10            self.folder_path = self.curr_dir
11
12        self.create_db_cwd(self.filename)
13        self.curr_filepath = self.curr_dir + r"\{}.db".format(filename)
14
15        if folder_path is None:
16            self.save_filepath(self.curr_filepath)
17            self.create_tables(self.curr_filepath)
18        else:
19            self.create_tables(self.curr_filepath)
20            self.move_file(filename, self.folder_path)
21            self.save_filepath(self.folder_path + r"\{}.db".format(filename))
22
23    def create_db_cwd(self, filename):
24        try:
25            f = open("{}.db".format(filename), "x")
26            f.close()
27        except FileExistsError:
28            mb.showerror('Error', 'File already exists')
29
30    def move_file(self, filename, new_folder_path):
31
32        # removes double quotations
33        pattern = "\""
34
35        if re.search(pattern, new_folder_path):
36            new_folder_path.replace('\"', '')
37
38        curr_filepath = self.curr_dir + r"\{}.db".format(filename)
39
40        try:
41            shutil.move(curr_filepath, new_folder_path)
42        except shutil.Error:
43            mb.showerror('Error', 'File already exists')
44
45    def create_tables(self, path):
46
47        try:
48            with sqlite3.connect(path) as db:
49                cursor = db.cursor()
50            cursor.execute('''
51            CREATE TABLE IF NOT EXISTS User(
52            ID INTEGER PRIMARY KEY,
53            username VARCHAR(20) NOT NULL UNIQUE,
54            password TEXT NOT NULL);
55            ''')
56            cursor.execute('''
57            CREATE TABLE IF NOT EXISTS Scores(
58            user_id INTEGER NOT NULL REFERENCES User(ID),
59            score INTEGER NOT NULL,
60            file_id INTEGER REFERENCES Files(file_id));
61            ''')
62            cursor.execute('''
63            CREATE TABLE IF NOT EXISTS Students(
64            user_id INTEGER NOT NULL REFERENCES User(ID),
65            school_id INTEGER NOT NULL REFERENCES Teachers(school_id),
66            firstname VARCHAR(20) NOT NULL,
67            lastname VARCHAR(20) NOT NULL);
68            ''')
69            cursor.execute('''
70            CREATE TABLE IF NOT EXISTS Teachers(
71            school_id INTEGER PRIMARY KEY,
72            user_id INTEGER NOT NULL REFERENCES User(ID),
73            firstname VARCHAR(20) NOT NULL,
74            lastname VARCHAR(20) NOT NULL);
75            ''')
76            cursor.execute('''
77            CREATE TABLE IF NOT EXISTS Schools(
78            school_id INTEGER REFERENCES Teachers(school_id),
79            user_id INTEGER NOT NULL REFERENCES User(ID));
80            ''')
81            cursor.execute('''
82            CREATE TABLE IF NOT EXISTS Assignments(
83            file_id INTEGER REFERENCES Files(file_id),
84            school_id INTEGER REFERENCES Teachers(school_id));
85            ''')
86            cursor.execute('''
87            CREATE TABLE IF NOT EXISTS Files(
88            file_id INTEGER PRIMARY KEY,
89            file VARCHAR(50) NOT NULL);
90            ''')
91            db.commit()
92            mb.showinfo('Info', 'Tables created')
93
94        except FileExistsError:
95            mb.showerror('Error', 'Tables already exist, no records changed')
96
97    def save_filepath(self, new_filepath):
98        try:
99            f = open("data_filepath.txt", "x")
100            f.close()
101            f = open("quiz_folder_path.txt", "x")
102            f.close()
103
104            with open("data_filepath.txt", "a") as f:
105                f.write("{}".format(new_filepath))
106
107            with open("quiz_folder_path.txt", "a") as f:
108                f.write(self.folder_path)
109
110        except FileExistsError:
111            mb.showerror('Error', 'File already exists')