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