· 9 years ago · Nov 14, 2016, 03:50 PM
1from tkinter import *
2from tkinter import messagebox
3import sqlite3, random, string, datetime, re
4
5############################################################
6class Database:
7def __init__(self, filename, tableName, tableFields):
8 self.filename = filename
9 self.tableName = tableName
10 self.tableFields = tableFields
11
12 #Get the raw name of each field
13 self.fieldNames = [i[0] for i in tableFields]
14
15 #Get the formatted name of each field
16 self.fieldNamesFormatted = [re.sub("([a-z])([A-Z])","g<1> g<2>",i).title() for i in self.fieldNames]
17
18 self.conn = sqlite3.connect(self.filename) #SQL
19 self.c = self.conn.cursor() #SQL
20
21 self.CreateTable()
22
23def CreateTable(self):
24 tableInfo=[]
25
26 #Get name, type and constraint of field
27 for i in self.tableFields:
28 tableInfo.append(i[0:3])
29
30 #Format it so that SQL will accept it
31 for count,i in enumerate(tableInfo):
32 tableInfo[count] = " ".join(i)
33 tableInfo = ", ".join(tableInfo)
34
35 self.c.execute("CREATE TABLE IF NOT EXISTS {table}({params})".format(table = self.tableName, params = tableInfo)) #SQL
36
37def AddEntry(self, params):
38
39 #Format it so that SQL will accept it
40 formattedFieldNamed = ", ".join(self.fieldNames)
41 questionMarks = ",".join(["?" for i in range(len(self.tableFields))])
42
43 self.c.execute("INSERT INTO {table} ({fieldNames}) VALUES ({questionMarks})".format(table = self.tableName, fieldNames = formattedFieldNamed, questionMarks = questionMarks), params) #SQL
44 self.conn.commit() #SQL
45
46def GetEntry(self,field,value):
47 self.c.execute("SELECT * FROM {table} WHERE {field} = ?".format(table = self.tableName, field = field), (value,)) #SQL
48 return self.c.fetchall() #SQL
49
50def GetAllEntries(self):
51 self.c.execute("SELECT * FROM {table}".format(table = self.tableName)) #SQL
52 return self.c.fetchall() #SQL
53
54def UpdateEntry(self, field, value, entryID):
55 self.c.execute("UPDATE {table} SET {field} = {value} WHERE ID = {entryID}".format(table = self.tableName, field = field, value = value, entryID = entryID)) #SQL
56 self.conn.commit() #SQL
57
58def DelEntry(self,field,value):
59 self.c.execute("DELETE FROM {table} WHERE {field} = ?".format(table = self.tableName, field = field), (value,)) #SQL
60 self.conn.commit() #SQL
61
62def Close(self):
63 self.c.close() #SQL
64 self.conn.close() #SQL
65
66############################################################
67class TableUI:
68def __init__(self, dataBase):
69 self.dataBase = dataBase
70
71 self.isReverse = False
72 self.CreateUI()
73
74def CreateUI(self):
75 self.root = Tk()
76 self.root.title(self.dataBase.tableName)
77 self.root.resizable(0,0)
78 self.root.config(bg = "#ececec")
79
80 self.root.bind("<Button-3>", self.RightClick)
81
82 self.root.canvas = Canvas(self.root, width = 1080, height=500)
83 self.root.frame = Frame(self.root.canvas)
84 self.scrollbar = Scrollbar(self.root, command=self.root.canvas.yview)
85 self.root.canvas.config(yscrollcommand=self.scrollbar.set)
86
87 self.root.canvas.grid(row=1,column=0,columnspan=len(self.dataBase.tableFields))
88 self.root.canvas.create_window((0,0), window=self.root.frame, anchor="nw",tags="self.root.frame")
89 self.scrollbar.grid(column=len(self.dataBase.tableFields),row=1,sticky="NS")
90
91 self.root.frame.bind("<Configure>", self.onFrameConfigure)
92
93
94 #Add the Column Headers
95 for countX, i in enumerate(self.dataBase.fieldNamesFormatted):
96 Button(self.root, text=i, width = 24, height = 2, relief=FLAT, bg = "#ececec", command = lambda number=countX : self.OrderColumnUI(self.dataBase.fieldNames[number])).grid(column=countX,row=0)
97
98 self.UpdateUI(False)
99
100def onFrameConfigure(self, event):
101 self.root.canvas.configure(scrollregion=self.root.canvas.bbox("all"))
102
103def UpdateUI(self,customList):
104 if customList == False:
105 customList = self.dataBase.GetAllEntries()
106 #Delete all the labels currently displayed
107 for widget in self.root.winfo_children():
108 if widget.winfo_class() == "Label":
109 widget.destroy()
110
111 #Create a label for each entry in the table
112 self.alternatedRows=[]
113 for countY, row in enumerate(customList):
114 for countX, entry in enumerate(row):
115 l = Label(self.root.frame, text=entry, width = 25, height = 1, bg="#FFFFFF")
116 l.grid(column=countX,row=countY+1)
117 if countY % 2 != 0:
118 self.alternatedRows.append(l)
119 l.config(bg="#f3f6fa")
120
121def OpenEntryUI(self):
122 self.entry = Tk()
123 self.entry.title("Add Entry")
124 self.entry.resizable(0,0)
125 self.entry.config(bg = "#ececec")
126 self.entry.focus_force()
127
128 #Create the labels/entry boxes for the Entry window
129 entryList = []
130 for iCount, i in enumerate(self.dataBase.tableFields):
131 label = Label(self.entry, text=self.dataBase.fieldNamesFormatted[iCount], bg ="#ececec", width=12, height=2, anchor=E)
132 label.grid(column=0,row=iCount,ipady=3)
133
134 #i[3] says how the field is represented in the entry screen
135 if i[3] == "ID":
136 entry = Entry(self.entry,width=25, relief = FLAT, bg = "#ececec")
137 entry.insert(0,random.randint(1,1000))
138 else:
139 entry = Entry(self.entry, width=25, relief = FLAT)
140
141 entry.grid(column=1,row=iCount,ipady=3)
142 entryList.append((entry,i[3]))
143
144 Button(self.entry, text="Submit", width = 35, height=2,relief = FLAT, bg ="#ff6060",fg="#FFFFFF", font=("",10,"bold"), command = lambda: self.SubmitEntryUI(entryList)).grid(column=0,columnspan=2,row=len(self.dataBase.fieldNames)+1,pady=(10,0))
145
146 return entryList
147
148def SubmitEntryUI(self,entryList):
149 invalidInputs = []
150 for count,i in enumerate(entryList):
151 if self.ValidInput(i[0].get(),i[1],False) == False:
152 invalidInputs.append(self.dataBase.fieldNamesFormatted[count])
153
154
155 if len(invalidInputs) == 0:
156 params = ([i[0].get() for i in entryList])
157 self.dataBase.AddEntry(params)
158 self.UpdateUI(False)
159 self.entry.destroy()
160 else:
161 messagebox.showerror("Invalid Input","Invalid {error}".format(error=", ".join(invalidInputs)))
162
163def DeleteEntryUI(self,selectedID):
164 if messagebox.askquestion("Delete Entry","Are you sure?") == "no": return
165
166 self.dataBase.DelEntry("ID",selectedID)
167 self.UpdateUI(False)
168
169def OpenEditEntryUI(self,selectedID):
170 selectedRow = self.dataBase.GetEntry("ID",selectedID)[0]
171 entryList = self.OpenEntryUI()
172 self.entry.title("Edit Entry")
173 for count,i in enumerate(entryList):
174 i[0].delete(0, END)
175 i[0].insert(0, selectedRow[count])
176
177 Button(self.entry, text="Update", width = 35, height=2,relief = FLAT, bg ="#ff6060",fg="#FFFFFF", font=("",10,"bold"),command = lambda: self.UpdateEntryUI(entryList)).grid(column=0,columnspan=2,row=len(self.dataBase.fieldNames)+1,pady=(10,0))
178
179def UpdateEntryUI(self,entryList):
180 invalidInputs = []
181 for count, i in enumerate(entryList):
182 if self.ValidInput(i[0].get(),i[1],True) == False:
183 invalidInputs.append(self.dataBase.fieldNamesFormatted[count])
184
185 if len(invalidInputs) == 0:
186 self.dataBase.UpdateEntry(self.dataBase.fieldNames[count],"""+i[0].get()+""",entryList[0][0].get())
187 self.UpdateUI(False)
188 self.entry.destroy()
189 else:
190 messagebox.showerror("Invalid Input","Invalid {error}".format(error=", ".join(invalidInputs)))
191
192def RightClick(self,event):
193 selectedID = 0
194
195 #Set up the right-click menu
196 self.menu = Menu(self.root, tearoff=0)
197 self.menu.add_command(label="Add", command = lambda: self.OpenEntryUI())
198 self.menu.add_command(label="Search", command = lambda: self.OpenSearchUI())
199 self.menu.add_separator()
200 self.menu.add_command(label="Edit", command = lambda: self.OpenEditEntryUI(selectedID))
201 self.menu.add_command(label="Delete", command = lambda: self.DeleteEntryUI(selectedID))
202 self.menu.add_separator()
203 self.menu.add_command(label="Refresh", command = lambda: self.UpdateUI(False))
204
205 #Find widgets on the row that was right clicked
206 for widget in self.root.frame.winfo_children():
207 mouseClickY = event.y_root - self.root.winfo_y() - widget.winfo_height()*3
208 widgetTopY = widget.winfo_y()
209 widgetBottomY = widget.winfo_y() + widget.winfo_height()
210
211 if (widget.winfo_class() == "Label") and (mouseClickY > widgetTopY) and (mouseClickY < widgetBottomY):
212 #Get ID of selected row
213 if selectedID == 0:
214 selectedID = int(widget.cget("text"))
215 #Highlight that row
216 if widget.cget("bg") != "#338fff":
217 widget.config(bg = "#338fff", fg="#FFFFFF")
218
219 #Deselect all rows
220 elif widget.winfo_class() == "Label":
221 widget.config(bg = "#FFFFFF", fg="#000000")
222 if widget in self.alternatedRows:
223 widget.config(bg="#f3f6fa")
224
225
226
227 #Make the popup menu
228 self.menu.post(event.x_root, event.y_root)
229
230def ValidInput(self, entry, dataType, ignoreUniqueID):
231 if dataType == "ID":
232 try: int(entry)
233 except: return False
234 if int(entry) > 1000: return False
235
236 if not ignoreUniqueID:
237 for i in self.dataBase.GetAllEntries():
238 if str(i[0]) == str(entry): return False
239
240 elif dataType == "NAME":
241 if len(entry) <= 1: return False
242 for i in entry:
243 if i not in string.ascii_letters: return False
244
245 elif dataType == "DATE":
246 try: datetime.datetime.strptime(entry, "%d/%m/%y").strftime('%d/%m/%y')
247 except: return False
248
249 elif dataType == "PHONE":
250 try: int(entry)
251 except: return False
252
253 if len(entry) <7: return False
254 if len(entry) >15: return False
255
256 elif dataType == "EMAIL":
257 if "@" not in list(entry) or "." not in list(entry): return False
258 else:
259 return False
260
261 return True
262
263def OrderColumnUI(self,columnName):
264 if self.isReverse: self.isReverse = False
265 else: self.isReverse = True
266
267
268 keyNum = self.dataBase.fieldNames.index(columnName)
269 self.UpdateUI(sorted(self.dataBase.GetAllEntries(), key = lambda field: field[keyNum], reverse = self.isReverse))
270
271def OpenSearchUI(self):
272 self.search = Tk()
273 self.search.title("Search")
274 self.search.resizable(0,0)
275 self.search.config(bg = "#ececec")
276 self.search.focus_force()
277
278 entry = Entry(self.search, width = 31, relief=FLAT)
279 entry.grid(column=0,row=0,ipady=4, pady = (10,5))
280
281 searchVar = StringVar(self.search)
282 searchVar.set(self.dataBase.fieldNamesFormatted[0]) # default value
283 fieldOptions = OptionMenu(self.search, searchVar, *self.dataBase.fieldNamesFormatted)
284 fieldOptions.config(width=25, relief=FLAT, bg ="#FFFFFF")
285 fieldOptions.grid(column=0,row=1, pady = (0,10))
286
287 searchButton = Button(self.search, text="Search", width=30, height=2,relief = FLAT, bg ="#ff6060",fg="#FFFFFF", font=("",10,"bold"), command = lambda: self.SearchUI(searchVar.get(),entry.get())).grid(column=0,row=2)
288
289def SearchUI(self, field, searchTerm):
290 field = self.dataBase.fieldNames[self.dataBase.fieldNamesFormatted.index(field)]
291 self.UpdateUI(self.dataBase.GetEntry(field, searchTerm))
292
293
294
295
296############################################################
297def createClientTable():
298 #["Name","Data Type","Constraint","Entry Field Type"]
299clientFields = [["ID","INT","PRIMARY KEY","ID"],
300 ["firstName","TEXT","NOT NULL","NAME"],
301 ["lastName","TEXT","NOT NULL","NAME"],
302 ["dateOfBirth","TEXT","NOT NULL","DATE"],
303 ["phoneNumber","TEXT","NOT NULL","PHONE"],
304 ["eMail","TEXT","NOT NULL","EMAIL"]]
305
306clientDB = Database("clientList.db", "clientTable", clientFields)
307clientUI = TableUI(clientDB)
308
309
310
311
312#Ensure that the program was run directly
313if __name__ == "__main__":
314createClientTable()