71vyk6n9

1import os
2import re
3import sqlite3
4
5
6class DbProvider:
7    def database_path(self, relative):
8        p = os.path.join(os.environ.get("_MEIPASS2", os.path.abspath(".")), relative)
9        print(p)
10        return p
11
12    def get_data_from_table(self, table_name):
13        #  Get headers of table
14        header_request = '''SELECT name FROM PRAGMA_TABLE_INFO('{}')'''.format(table_name)
15        self.cursor.execute(header_request)
16        headers = self.cursor.fetchall()
17
18        #  Get rows of table
19        sql = '''SELECT * FROM {}'''.format(table_name)
20        self.cursor.execute(sql)
21        rows = self.cursor.fetchall()
22
23        #  Create dict
24        dataset = {}
25        data = {}
26        j = 0
27        for row in rows:
28            i = 0
29            for item in row:
30                data[str(headers[i][0])] = str(item)
31                i += 1
32            dataset[str(j)] = data.copy()
33            j += 1
34            data.clear()
35        self.dataSet.clear()
36        self.dataSet = dataset
37        return dataset
38
39    def __init__(self):
40        self.path = self.database_path('DB\db.sqlite')
41        self.dataSet = {}
42
43        if not os.path.isfile(self.path):
44            try:
45                os.mkdir(self.database_path("DB"))
46            except:
47                pass
48        # Create database if not exist and get a connection to it
49        self.connection = sqlite3.connect(self.path)
50        # Get a cursor to execute sql statements
51        self.cursor = self.connection.cursor()
52
53        # Create tables
54        self.custom_face = self.CustomFace(self)
55        self.face = self.Face(self)
56        self.eye = self.Eye(self)
57        self.eyes = self.Eyes(self)
58        self.ear = self.Ear(self)
59        self.ears = self.Ears(self)
60        self.eyeBrow = self.EyeBrow(self)
61        self.eyeBrows = self.EyeBrows(self)
62        self.nose = self.Nose(self)
63        self.lips = self.Mouth(self)
64        self.hair = self.Hair(self)
65        self.AttributeAssignment = self.AttributeAssignment(self)
66        self.appearance = self.Appearance(self)
67
68    def get_dict(self, headers, rows):  # Create dict
69        dataset = {}
70        data = {}
71        j = 0
72        for row in rows:
73            i = 0
74            for item in row:
75                data[str(headers[i])] = str(item)
76                i += 1
77            dataset[str(j)] = data.copy()
78            j += 1
79            data.clear()
80        self.dataSet.clear()
81        self.dataSet = dataset
82        return dataset
83
84    def custom_select(self, select_formula):
85        headers = []
86        x = re.split("(?i)FROM", select_formula)
87        headers = re.findall(r'(\w+)+', re.split("(?i)SELECT", x[0])[1], re.IGNORECASE)
88        if headers == []:
89            headers = re.findall(r'([*]+)', re.split("(?i)SELECT", x[0])[1], re.IGNORECASE)
90
91        table_name = re.findall(r'(\w+)+', x[1], re.IGNORECASE)[0]
92        # Get headers of table
93        if (headers[0] == '*'):
94            header_request = '''SELECT name FROM PRAGMA_TABLE_INFO('{}')'''.format(table_name)
95            self.cursor.execute(header_request)
96            headers = self.cursor.fetchall()
97            new_headers = []
98            for h in headers:
99                new_headers.append(h[0])
100            headers = new_headers
101        #  Get rows of table
102        sql = select_formula
103        try:
104            self.cursor.execute(sql)
105        except (e):
106            print(e)
107        rows = self.cursor.fetchall()
108        return self.get_dict(headers, rows)
109
110    class Face:
111        def insert_into_table(self, skintype, size, path):
112            sql = '''INSERT INTO Face (SkinType, Size, Path) VALUES ('{}','{}','{}');'''.format(skintype, size, path)
113            self.cursor.execute(sql)
114            self.connection.commit()
115
116        def __init__(self, parent):
117            self.connection = parent.connection
118            self.cursor = parent.cursor
119            sql = '''CREATE TABLE IF NOT EXISTS Face
120            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
121            SkinType VARCHAR(100),
122            Size VARCHAR(20),
123            Path VARCHAR(300)
124            )'''
125            self.cursor.execute(sql)
126            self.connection.commit()
127
128    class Eyes:
129        def insert_into_table(self, skintype, L_ID, R_ID):
130            sql = '''INSERT INTO Eyes (SkinType, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
131            self.cursor.execute(sql)
132            self.connection.commit()
133
134        def __init__(self, parent):
135            self.connection = parent.connection
136            self.cursor = parent.cursor
137            sql = '''CREATE TABLE IF NOT EXISTS Eyes
138            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
139            SkinType VARCHAR(100),
140            L_ID INT,
141            R_ID INT,
142            FOREIGN KEY(L_ID) references Eye(ID),
143            FOREIGN KEY(R_ID) references Eye(ID)
144            )'''
145            self.cursor.execute(sql)
146            self.connection.commit()
147
148    class Eye:
149        def insert_into_table(self, size, colour, skintype, side, path, legacy):
150            sql = '''INSERT INTO Eye (Size, Colour, SkinType, Side, Path, Legacy) VALUES ('{}', '{}' ,'{}','{}','{}','{}');'''.format(
151                size, colour, skintype, side, path, legacy)
152            self.cursor.execute(sql)
153            self.connection.commit()
154
155        def __init__(self, parent):
156            self.connection = parent.connection
157            self.cursor = parent.cursor
158            sql = '''CREATE TABLE IF NOT EXISTS Eye
159            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
160            Size VARCHAR(100),
161            Colour VARCHAR(100),
162            SkinType VARCHAR(100),
163            Side VARCHAR(100),
164            Path VARCHAR(300),
165            Legacy VARCHAR(100)
166            )'''
167            self.cursor.execute(sql)
168            self.connection.commit()
169
170    class Nose:
171        def insert_into_table(self, size, skintype, path):
172            sql = '''INSERT INTO Nose (Size,SkinType,Path) VALUES ('{}',{},'{}');'''.format(size, skintype, path)
173            self.cursor.execute(sql)
174            self.connection.commit()
175
176        def __init__(self, parent):
177            self.connection = parent.connection
178            self.cursor = parent.cursor
179            sql = '''CREATE TABLE IF NOT EXISTS Nose
180            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
181            Size VARCHAR(100),
182            SkinType VARCHAR(100),
183            Path VARCHAR(300)
184            )'''
185            self.cursor.execute(sql)
186            self.connection.commit()
187
188    class Mouth:
189        def insert_into_table(self, size, skintype, path):
190            sql = '''INSERT INTO Lips (SkinType, Size, Colour, Path) VALUES ('{}',{},'{}');'''.format(size,
191                                                                                                           skintype,
192                                                                                                           path)
193            self.cursor.execute(sql)
194            self.connection.commit()
195
196        def __init__(self, parent):
197            self.connection = parent.connection
198            self.cursor = parent.cursor
199            sql = '''CREATE TABLE IF NOT EXISTS Mouth
200            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
201            SkinType VARCHAR(100),
202            Size VARCHAR(100),
203            Path VARCHAR(300)
204            )'''
205            self.cursor.execute(sql)
206            self.connection.commit()
207
208    class Ears:
209        def insert_into_table(self, skintype, L_ID, R_ID):
210            sql = '''INSERT INTO Ears (Shape, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
211            self.cursor.execute(sql)
212            self.connection.commit()
213
214        def __init__(self, parent):
215            self.connection = parent.connection
216            self.cursor = parent.cursor
217            sql = '''CREATE TABLE IF NOT EXISTS Ears
218            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
219            SkinType VARCHAR(100),
220            L_ID INT,
221            R_ID INT,
222            FOREIGN KEY(L_ID) references Eye(ID),
223            FOREIGN KEY(R_ID) references Eye(ID)
224            )'''
225            self.cursor.execute(sql)
226            self.connection.commit()
227
228    class Ear:
229        def insert_into_table(self, size, skintype, side, path, legacy):
230            sql = '''INSERT INTO Ear (Size, SkinType, Side, Path) VALUES ('{}',{},'{}','{}','{}');'''.format(size,
231                                                                                                             skintype,
232                                                                                                             side,
233                                                                                                             path,
234                                                                                                             legacy)
235            self.cursor.execute(sql)
236            self.connection.commit()
237
238        def __init__(self, parent):
239            self.connection = parent.connection
240            self.cursor = parent.cursor
241            sql = '''CREATE TABLE IF NOT EXISTS Ear
242            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
243            SkinType VARCHAR(100),
244            Side VARCHAR(100),
245            Path VARCHAR(300),
246            Legacy VARCHAR(100)
247            )'''
248            self.cursor.execute(sql)
249            self.connection.commit()
250
251    class EyeBrows:
252        def insert_into_table(self, skintype, L_ID, R_ID):
253            sql = '''INSERT INTO Ears (Shape, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
254            self.cursor.execute(sql)
255            self.connection.commit()
256
257        def __init__(self, parent):
258            self.connection = parent.connection
259            self.cursor = parent.cursor
260            sql = '''CREATE TABLE IF NOT EXISTS EyeBrows
261            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
262            SkinType VARCHAR(100),
263            L_ID INT,
264            R_ID INT,
265            FOREIGN KEY(L_ID) references EyeBrow(ID),
266            FOREIGN KEY(R_ID) references EyeBrow(ID)
267            )'''
268            self.cursor.execute(sql)
269            self.connection.commit()
270
271    class EyeBrow:
272        def insert_into_table(self, size, colour, skintype, side, path, legacy):
273            sql = '''INSERT INTO Ear (Size, Colour, SkinType, Side, Path, Legacy) VALUES ('{}','{}','{}','{}','{}','{}');'''.format(
274                size, colour, skintype,
275                side,
276                path, legacy)
277            self.cursor.execute(sql)
278            self.connection.commit()
279
280        def __init__(self, parent):
281            self.connection = parent.connection
282            self.cursor = parent.cursor
283            sql = '''CREATE TABLE IF NOT EXISTS EyeBrow
284            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
285            Size VARCHAR(100),
286            Colour VARCHAR(100),
287            SkinType VARCHAR(100),
288            Side VARCHAR(100),
289            Path VARCHAR(300),
290            Legacy VARCHAR(100)
291            )'''
292            self.cursor.execute(sql)
293            self.connection.commit()
294
295    class Hair:
296        def insert_into_table(self, hairtype, colour, skintype, path):
297            sql = '''INSERT INTO Hair (HairType, Colour, SkinType, Path) VALUES ('{}','{}','{}','{}');'''.format(
298                hairtype, colour, skintype, path)
299            self.cursor.execute(sql)
300            self.connection.commit()
301
302        def __init__(self, parent):
303            self.connection = parent.connection
304            self.cursor = parent.cursor
305            sql = '''CREATE TABLE IF NOT EXISTS Hair
306            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
307            HairType VARCHAR(100),
308            Colour VARCHAR(100),
309            SkinType VARCHAR(100),
310            Path VARCHAR(300)
311            )'''
312            self.cursor.execute(sql)
313            self.connection.commit()
314
315    class AttributeAssignment:  # AttributeAssignment
316        def insert_into_table(self, faceID, eyesID, eyebrowsID, noseID, mouthID, earsID, hairID):
317            sql = '''INSERT INTO AttributeAssignment (FaceID, EyesID, NoseID, MouthID, EarsID, HairID) 
318            VALUES ({},{},{},{},{},{});'''.format(faceID, eyesID, noseID, mouthID, earsID, hairID)
319            self.cursor.execute(sql)
320            self.connection.commit()
321
322        def __init__(self, parent):
323            self.connection = parent.connection
324            self.cursor = parent.cursor
325            sql = '''CREATE TABLE IF NOT EXISTS AttributeAssignment
326            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
327            FaceID INTEGER,
328            EyesID INTEGER,
329            NoseID INTEGER, 
330            MouthID INTEGER, 
331            EarsID INTEGER, 
332            HairID INTEGER,
333            FOREIGN KEY(FaceID) references Face(ID),
334            FOREIGN KEY(EyesID) references Eyes(ID),
335            FOREIGN KEY(NoseID) references Nose(ID),
336            FOREIGN KEY(MouthID) references Mouth(ID),
337            FOREIGN KEY(EarsID) references Ears(ID),
338            FOREIGN KEY(HairID) references Hair(ID)
339            )'''
340            self.cursor.execute(sql)
341            self.connection.commit()
342
343    class Appearance:  # Appearance
344        def insert_into_table(self, assignmentID, skintype, filePath):
345            sql = '''INSERT INTO Appearance (AssignmentID, SkinType, Date, Path) VALUES ({},'{}',DATETIME('now','localtime'),'{}');'''.format(
346                assignmentID, skintype, filePath)
347            self.cursor.execute(sql)
348            self.connection.commit()
349
350        def __init__(self, parent):
351            self.connection = parent.connection
352            self.cursor = parent.cursor
353            sql = '''CREATE TABLE IF NOT EXISTS Appearance
354            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
355            AssignmentID INTEGER,
356            SkinType VARCHAR(100),
357            Date VARCHAR(100),
358            Path varchar(300),
359            FOREIGN KEY(AssignmentID) references AttributeAssignment(ID)
360            )'''
361            self.cursor.execute(sql)
362            self.connection.commit()
363
364    class CustomFace:
365        def insert_into_table(self, name, genetic, path):
366            sql = '''INSERT INTO CustomFace (Name, Genetic, Path) VALUES ('{}', '{}','{}');'''.format(name, genetic,
367                                                                                                      path)
368            self.cursor.execute(sql)
369            self.connection.commit()
370
371        def __init__(self, parent):
372            self.connection = parent.connection
373            self.cursor = parent.cursor
374            sql = '''CREATE TABLE IF NOT EXISTS CustomFace
375            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
376            Name varchar(100),
377            Genetic varchar(100),
378            Path varchar(100)
379            )'''
380            self.cursor.execute(sql)
381            self.connection.commit()
382
383
384def addEyes(size, color, skintype, legacy):
385    db.eye.insert_into_table(size, color, skintype, "left", "Files/Features/" + legacy + "/l_eye.png", legacy)
386    db.eye.insert_into_table(size, color, skintype, "right", "Files/Features/" + legacy + "/r_eye.png", legacy)
387    lefyEye = \
388        db.custom_select("SELECT ID FROM Eye e WHERE e.Side=LOWER('left') AND e.Legacy=LOWER('{}')".format(legacy))[
389            '0'].get('ID')
390    rightEye = \
391        db.custom_select("SELECT ID FROM Eye e WHERE e.Side=LOWER('right') AND e.Legacy=LOWER('{}')".format(legacy))[
392            '0'].get('ID')
393    db.eyes.insert_into_table(skintype, lefyEye, rightEye)
394
395
396def addEars(size, skintype, legacy):
397    db.ear.insert_into_table(size, skintype, "left", "Files/Features/" + legacy + "/l_ear.png", legacy)
398    db.ear.insert_into_table(size, skintype, "right", "Files/Features/" + legacy + "/r_ear.png", legacy)
399    leftEar = \
400        db.custom_select("SELECT ID FROM Ear e WHERE e.Side=LOWER('left') AND e.Legacy=LOWER('{}')".format(legacy))[
401            '0'].get('ID')
402    rightEar = \
403        db.custom_select("SELECT ID FROM Ear e WHERE e.Side=LOWER('right') AND e.Legacy=LOWER('{}')".format(legacy))[
404            '0'].get('ID')
405    db.ears.insert_into_table(skintype, leftEar, rightEar)
406
407
408def addEyeBrows(size, color, skintype, legacy):
409    db.eyeBrow.insert_into_table(size, color, skintype, "left", "Files/Features/" + legacy + "/l_eyebrow.png", legacy)
410    db.eyeBrow.insert_into_table(size, color, skintype, "right", "Files/Features/" + legacy + "/r_eyebrow.png", legacy)
411    leftEyeBrow = \
412        db.custom_select("SELECT ID FROM EyeBrow e WHERE e.Side=LOWER('left') AND e.Legacy=LOWER('{}')".format(legacy))[
413            '0'].get('ID')
414    rightEyeBrow = \
415        db.custom_select(
416            "SELECT ID FROM EyeBrow e WHERE e.Side=LOWER('right') AND e.Legacy=LOWER('{}')".format(legacy))[
417            '0'].get('ID')
418    db.ears.insert_into_table(skintype, leftEyeBrow, rightEyeBrow)
419
420
421def addNose(size, skintype, legacy):
422    db.nose.insert_into_table(size, skintype, "Files/Features/" + legacy + "/nose.png")
423
424
425def addMouth(size, skintype, legacy):
426    db.mouth.insert_into_table(size, skintype, "Files/Features/" + legacy + "/mouth.png")
427
428
429# insert
430# insert
431# id_1 = select
432# id_2 = select
433# insert 3 (id_1, id_2)
434
435if __name__ == "__main__":
436    db = DbProvider()
437    # db.custom_face.insert_into_table("Face1","Europe","DatabaseBackend/Files/Faces/Face-01/face-1.jpg")
438    # db.eye.insert_into_table("Big", "blue", "white", "left", "dupa")
439    # db.custom_face.insert_into_table("Face2","Africa","DatabaseBackend/Files/Faces/Face-02/face-2.jpg")
440    # db.custom_face.insert_into_table("Face3", "Europe", "DatabaseBackend/Files/Faces/Face-03/face-3.jpg")
441    # db.custom_face.insert_into_table("Face4", "Asia", "DatabaseBackend/Files/Faces/face-5.jpg")
442    # db.get_data_from_table("CustomFace")
443    # db.insert_into_table('Face', 'tempName')
444    # db.get_data_from_table('Face')
445    # db.eye.insert_into_table("small", "asd", "asd", "left", "dupa", "qwe")
446    # db.eye.insert_into_table("small", "blue", "white", "left", "Files/Features/InputFace-00/l_eye.png", "InputFace-00")
447    # db.eye.insert_into_table("small", "blue", "white", "right", "Files/Features/InputFace-00/r_eye.png", "InputFace-00")
448    # lefyEye = db.custom_select("SELECT ID FROM Eye e WHERE e.Side=LOWER('left') AND e.Legacy=LOWER('InputFace-00')")[
449    #     '0'].get('ID')
450    # rightEye = db.custom_select("SELECT ID FROM Eye e WHERE e.Side=LOWER('right') AND e.Legacy=LOWER('InputFace-00')")[
451    #     '0'].get('ID')
452    # db.eyes.insert_into_table("white", lefyEye, rightEye)
453
454    addEyes("small", "blue", "white", "InputFace-00")
455    addEars()