· 5 years ago · Feb 26, 2020, 08:34 PM
1import os
2import sqlite3
3
4
5class DbProvider:
6 def database_path(self, relative):
7 p = os.path.join(os.environ.get("_MEIPASS2", os.path.abspath(".")), relative)
8 print(p)
9 return p
10
11 def get_data_from_table(self, table_name):
12 # Get headers of table
13 header_request = '''SELECT name FROM PRAGMA_TABLE_INFO('{}')'''.format(table_name)
14 self.cursor.execute(header_request)
15 headers = self.cursor.fetchall()
16
17 # Get rows of table
18 sql = '''SELECT * FROM {}'''.format(table_name)
19 self.cursor.execute(sql)
20 rows = self.cursor.fetchall()
21
22 # Create dict
23 dataset = {}
24 data = {}
25 j = 0
26 for row in rows:
27 i = 0
28 for item in row:
29 data[str(headers[i][0])] = str(item)
30 i += 1
31 dataset[str(j)] = data.copy()
32 j += 1
33 data.clear()
34 self.dataSet.clear()
35 self.dataSet = dataset
36 return dataset
37
38 def __init__(self):
39 self.path = self.database_path('DB\db.sqlite')
40 self.dataSet = {}
41
42 if not os.path.isfile(self.path):
43 try:
44 os.mkdir(self.database_path("DB"))
45 except:
46 pass
47 # Create database if not exist and get a connection to it
48 self.connection = sqlite3.connect(self.path)
49 # Get a cursor to execute sql statements
50 self.cursor = self.connection.cursor()
51
52 # Create tables
53 self.custom_face = self.CustomFace(self)
54 self.face = self.Face(self)
55 self.eyes = self.Eyes(self)
56 self.nose = self.Nose(self)
57 self.lips = self.Mouth(self)
58 self.ears = self.Ears(self)
59 self.hair = self.Hair(self)
60 self.AttributeAssignment = self.AttributeAssignment(self)
61 self.appearance = self.Appearance(self)
62
63 class Face:
64 def insert_into_table(self, skintype, size, path):
65 sql = '''INSERT INTO Face (SkinType, Size, Path) VALUES ('{}','{}','{}');'''.format(skintype, size, path)
66 self.cursor.execute(sql)
67 self.connection.commit()
68
69 def __init__(self, parent):
70 self.connection = parent.connection
71 self.cursor = parent.cursor
72 sql = '''CREATE TABLE IF NOT EXISTS Face
73 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
74 SkinType VARCHAR(100),
75 Size VARCHAR(20),
76 Path VARCHAR(300)
77 )'''
78 self.cursor.execute(sql)
79 self.connection.commit()
80
81 class Eyes:
82 def insert_into_table(self, skintype, L_ID, R_ID):
83 sql = '''INSERT INTO Eyes (SkinType, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
84 self.cursor.execute(sql)
85 self.connection.commit()
86
87 def __init__(self, parent):
88 self.connection = parent.connection
89 self.cursor = parent.cursor
90 sql = '''CREATE TABLE IF NOT EXISTS Eyes
91 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
92 SkinType VARCHAR(100),
93 L_ID INT,
94 R_ID INT,
95 FOREIGN KEY(L_ID) references Eye(ID),
96 FOREIGN KEY(R_ID) references Eye(ID),
97 )'''
98 self.cursor.execute(sql)
99 self.connection.commit()
100
101 class Eye:
102 def insert_into_table(self, size, colour, skintype, side, path):
103 sql = '''INSERT INTO Eye (Size, Colour, SkinType, Side, Path) VALUES ('{}',{},'{}','{}','{}');'''.format(
104 size, colour, skintype, side, path)
105 self.cursor.execute(sql)
106 self.connection.commit()
107
108 def __init__(self, parent):
109 self.connection = parent.connection
110 self.cursor = parent.cursor
111 sql = '''CREATE TABLE IF NOT EXISTS Eye
112 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
113 Size VARCHAR(100),
114 Colour VARCHAR(100),
115 SkinType VARCHAR(100),
116 Side VARCHAR(100),
117 Path VARCHAR(300)
118 )'''
119 self.cursor.execute(sql)
120 self.connection.commit()
121
122 class Nose:
123 def insert_into_table(self, skintype, size, path):
124 sql = '''INSERT INTO Nose (SkinType,Size,Path) VALUES ('{}',{},'{}');'''.format(skintype, size, path)
125 self.cursor.execute(sql)
126 self.connection.commit()
127
128 def __init__(self, parent):
129 self.connection = parent.connection
130 self.cursor = parent.cursor
131 sql = '''CREATE TABLE IF NOT EXISTS Nose
132 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
133 SkinType VARCHAR(100),
134 Size VARCHAR(100),
135 Path VARCHAR(300)
136 )'''
137 self.cursor.execute(sql)
138 self.connection.commit()
139
140 class Mouth:
141 def insert_into_table(self, skintype, size, colour, path):
142 sql = '''INSERT INTO Lips (SkinType, Size, Colour, Path) VALUES ('{}',{},'{}','{}');'''.format(skintype,
143 size,
144 colour, path)
145 self.cursor.execute(sql)
146 self.connection.commit()
147
148 def __init__(self, parent):
149 self.connection = parent.connection
150 self.cursor = parent.cursor
151 sql = '''CREATE TABLE IF NOT EXISTS Mouth
152 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
153 SkinType VARCHAR(100),
154 Size VARCHAR(100),
155 Colour VARCHAR(100),
156 Path VARCHAR(300)
157 )'''
158 self.cursor.execute(sql)
159 self.connection.commit()
160
161 class Ears:
162 def insert_into_table(self, skintype, L_ID, R_ID):
163 sql = '''INSERT INTO Ears (Shape, L_ID, R_ID) VALUES ('{}',{},{});'''.format(skintype, L_ID, R_ID)
164 self.cursor.execute(sql)
165 self.connection.commit()
166
167 def __init__(self, parent):
168 self.connection = parent.connection
169 self.cursor = parent.cursor
170 sql = '''CREATE TABLE IF NOT EXISTS Ears
171 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
172 SkinType VARCHAR(100),
173 L_ID INT,
174 R_ID INT,
175 FOREIGN KEY(L_ID) references Eye(ID),
176 FOREIGN KEY(R_ID) references Eye(ID),
177 )'''
178 self.cursor.execute(sql)
179 self.connection.commit()
180
181 class Ear:
182 def insert_into_table(self, size, skintype, side, path):
183 sql = '''INSERT INTO Ear (Size, SkinType, Side, Path) VALUES ('{}',{},'{}','{}');'''.format(size, skintype, side,
184 path)
185 self.cursor.execute(sql)
186 self.connection.commit()
187
188 def __init__(self, parent):
189 self.connection = parent.connection
190 self.cursor = parent.cursor
191 sql = '''CREATE TABLE IF NOT EXISTS Ear
192 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
193 SkinType VARCHAR(100),
194 Side VARCHAR(100),
195 Path VARCHAR(300)
196 )'''
197 self.cursor.execute(sql)
198 self.connection.commit()
199
200 class Hair:
201 def insert_into_table(self, hairtype, colour, skintype, path):
202 sql = '''INSERT INTO Hair (HairType, Colour, SkinType, Path) VALUES ('{}','{}','{}','{}');'''.format(hairtype, colour, skintype, path)
203 self.cursor.execute(sql)
204 self.connection.commit()
205
206 def __init__(self, parent):
207 self.connection = parent.connection
208 self.cursor = parent.cursor
209 sql = '''CREATE TABLE IF NOT EXISTS Hair
210 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
211 HairType VARCHAR(100),
212 Colour VARCHAR(100),
213 SkinType VARCHAR(100),
214 Path VARCHAR(300)
215 )'''
216 self.cursor.execute(sql)
217 self.connection.commit()
218
219 class AttributeAssignment: # AttributeAssignment
220 def insert_into_table(self, faceID, eyesID, eyebrowsID, noseID, mouthID, earsID, hairID):
221 sql = '''INSERT INTO AttributeAssignment (FaceID, EyesID, NoseID, MouthID, EarsID, HairID)
222 VALUES ({},{},{},{},{},{});'''.format(faceID, eyesID, noseID, mouthID, earsID, hairID)
223 self.cursor.execute(sql)
224 self.connection.commit()
225
226 def __init__(self, parent):
227 self.connection = parent.connection
228 self.cursor = parent.cursor
229 sql = '''CREATE TABLE IF NOT EXISTS AttributeAssignment
230 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
231 FaceID INTEGER,
232 EyesID INTEGER,
233 NoseID INTEGER,
234 MouthID INTEGER,
235 EarsID INTEGER,
236 HairID INTEGER,
237 FOREIGN KEY(FaceID) references Face(ID),
238 FOREIGN KEY(EyesID) references Eyes(ID),
239 FOREIGN KEY(NoseID) references Nose(ID),
240 FOREIGN KEY(MouthID) references Mouth(ID),
241 FOREIGN KEY(EarsID) references Ears(ID),
242 FOREIGN KEY(HairID) references Hair(ID)
243 )'''
244 self.cursor.execute(sql)
245 self.connection.commit()
246
247 class Appearance: # Appearance
248 def insert_into_table(self, assignmentID, skintype, filePath):
249 sql = '''INSERT INTO Appearance (AssignmentID, SkinType, Date, Path) VALUES ({},'{}',DATETIME('now','localtime'),'{}');'''.format(
250 assignmentID, skintype, filePath)
251 self.cursor.execute(sql)
252 self.connection.commit()
253
254 def __init__(self, parent):
255 self.connection = parent.connection
256 self.cursor = parent.cursor
257 sql = '''CREATE TABLE IF NOT EXISTS Appearance
258 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
259 AssignmentID INTEGER,
260 SkinType VARCHAR(100),
261 Date VARCHAR(100)
262 Path varchar(300),
263 FOREIGN KEY(AssignmentID) references AttributeAssignment(ID)
264 )'''
265 self.cursor.execute(sql)
266 self.connection.commit()
267
268 class CustomFace:
269 def insert_into_table(self, name, genetic, path):
270 sql = '''INSERT INTO CustomFace (Name, Genetic, Path) VALUES ('{}', '{}','{}');'''.format(name, genetic,
271 path)
272 self.cursor.execute(sql)
273 self.connection.commit()
274
275 def __init__(self, parent):
276 self.connection = parent.connection
277 self.cursor = parent.cursor
278 sql = '''CREATE TABLE IF NOT EXISTS CustomFace
279 (ID INTEGER PRIMARY KEY AUTOINCREMENT,
280 Name varchar(100),
281 Genetic varchar(100),
282 Path varchar(100)
283 )'''
284 self.cursor.execute(sql)
285 self.connection.commit()
286
287
288if __name__ == "__main__":
289 db = DbProvider()
290 # db.custom_face.insert_into_table("Face1","Europe","DatabaseBackend/Files/Faces/Face-01/face-1.jpg")
291 # db.custom_face.insert_into_table("Face2","Africa","DatabaseBackend/Files/Faces/Face-02/face-2.jpg")
292 # db.custom_face.insert_into_table("Face3", "Europe", "DatabaseBackend/Files/Faces/Face-03/face-3.jpg")
293 # db.custom_face.insert_into_table("Face4", "Asia", "DatabaseBackend/Files/Faces/face-5.jpg")
294 db.get_data_from_table("CustomFace")
295 # db.insert_into_table('Face', 'tempName')
296 # db.get_data_from_table('Face')