Creating A CRUD Desktop Application Using Python3 And MySQL Database Server

Introduction 

 
This is a simple CRUD(Create, Retrieve, Update, and Delete) database record desktop application. Its name is Student Management System. A record with Student’s FirstName, LastName, ContactNo, City, State, and date of birth are inserted into a table named student_master in the student database. MySQL Database Server. Python 3.6, Tkinter, GUI builder, and MySQL-connector are used. For choosing the date of birth, the tkcalendar widget is used. Installation of the tkcalender widget from the command prompt is displayed below.
 
 
The name of the Python code file is StudentManager.py. After executing the file, the application window is displayed below. In that window, a new student record can be added. All the added records in MySQL database table are displayed in a Tkinter Treeview widget, shown below.
 
 

Inserting Student Record

 
All the student data is filled up and the date of birth is chosen from a drop-down calendar widget, as shown below, and the register button is clicked to add the record to the database.
 
The Student Record is inserted successfully and displayed below.
 
 
If the inserted record state name is spelled wrong or we want to change the date of birth, the record must be updated. Please select the record in the displayed widget by clicking it. All data is displayed in the above boxes to update and delete the record.
 
 
 
Please edit the fields displayed in boxes and update them by clicking the Update button.
 
 
A student record can be searched by entering roll no and clicking the search button as shown above.
 
To display all records, please click the Show All button.
 
 
To Delete a record, select the record in the display widget and click the delete button.
 
 
 
StudentManager.py Code
  1. from tkcalendar import Calendar, DateEntry  
  2. import tkinter as tk  
  3. import tkinter.messagebox as mb  
  4. import tkinter.ttk as ttk  
  5. ## Connecting to the database  
  6. ## importing 'mysql.connector' for connection to mysql database  
  7. import mysql.connector  
  8. ## connecting to the database using 'connect()' method  
  9. ## it takes 3 required parameters 'host', 'user', 'password'  
  10. #Please change user and password values to your  
  11. #user and password values to connect to MySQL Database server   
  12. db_connection = mysql.connector.connect(  
  13. host="localhost",  
  14. user="raichand70",  
  15. password="1America")  
  16. # creating database_cursor to perform SQL operation  
  17. db_cursor = db_connection.cursor(buffered=True# "buffered=True".makes db_cursor.row_count return actual number of records selected otherwise would return -1  
  18. class StudentApp(tk.Tk):  
  19. def __init__(self):  
  20. super().__init__()  
  21. self.title("Student Management System")  
  22. self.geometry("800x650+351+174")  
  23. self.lblTitle = tk.Label(self, text="Student Management System", font=("Helvetica"16), bg="yellow", fg="green")  
  24. self.lblFName = tk.Label(self, text="Enter FirstName:", font=("Helvetica"10), bg="blue", fg="yellow")  
  25. self.lblLName = tk.Label(self, text="Enter LastName:", font=("Helvetica"10), bg="blue", fg="yellow")  
  26. self.lblContactNo = tk.Label(self, text="Enter Contact No:", font=("Helvetica"10), bg="blue", fg="yellow")  
  27. self.lblCity = tk.Label(self, text="Enter City:", font=("Helvetica"10), bg="blue", fg="yellow")  
  28. self.lblState = tk.Label(self, text="Enter State:", font=("Helvetica"10), bg="blue", fg="yellow")  
  29. self.lblDOB = tk.Label(self, text="Choose Date of Birth:", font=("Helvetica"10), bg="blue", fg="yellow")  
  30. self.lblSelect = tk.Label(self, text="Please select one record below to update or delete", font=("Helvetica"10), bg="blue", fg="yellow")  
  31. self.lblSearch = tk.Label(self, text="Please Enter Roll No:",font=("Helvetica"10), bg="blue", fg="yellow")  
  32. self.entFName = tk.Entry(self)  
  33. self.entLName = tk.Entry(self)  
  34. self.entContact = tk.Entry(self)  
  35. self.entCity = tk.Entry(self)  
  36. self.entState = tk.Entry(self)  
  37. self.calDOB = DateEntry(self, width=12, background='darkblue',  
  38. foreground='white', borderwidth=2, year=1950,locale='en_US', date_pattern='y-mm-dd')  
  39. #self.entDOB = tk.Entry(self)  
  40. self.entSearch = tk.Entry(self)  
  41. self.btn_register = tk.Button(self, text="Register", font=("Helvetica"11), bg="yellow", fg="blue",  
  42. command=self.register_student)  
  43. self.btn_update = tk.Button(self,text="Update",font=("Helvetica",11),bg="yellow", fg="blue",command=self.update_student_data)  
  44. self.btn_delete = tk.Button(self, text="Delete", font=("Helvetica"11), bg="yellow", fg="blue",  
  45. command=self.delete_student_data)  
  46. self.btn_clear = tk.Button(self, text="Clear", font=("Helvetica"11), bg="yellow", fg="blue",  
  47. command=self.clear_form)  
  48. self.btn_show_all = tk.Button(self, text="Show All", font=("Helvetica"11), bg="yellow", fg="blue",  
  49. command=self.load_student_data)  
  50. self.btn_search = tk.Button(self, text="Search", font=("Helvetica"11), bg="yellow", fg="blue",  
  51. command=self.show_search_record)  
  52. self.btn_exit = tk.Button(self, text="Exit", font=("Helvetica"16), bg="yellow", fg="blue",command=self.exit)  
  53. columns = ("#1""#2""#3""#4""#5""#6""#7")  
  54. self.tvStudent= ttk.Treeview(self,show="headings",height="5", columns=columns)  
  55. self.tvStudent.heading('#1', text='RollNo', anchor='center')  
  56. self.tvStudent.column('#1', width=60, anchor='center', stretch=False)  
  57. self.tvStudent.heading('#2', text='FirstName', anchor='center')  
  58. self.tvStudent.column('#2', width=10, anchor='center', stretch=True)  
  59. self.tvStudent.heading('#3', text='LastName', anchor='center')  
  60. self.tvStudent.column('#3',width=10, anchor='center', stretch=True)  
  61. self.tvStudent.heading('#4', text='City', anchor='center')  
  62. self.tvStudent.column('#4',width=10, anchor='center', stretch=True)  
  63. self.tvStudent.heading('#5', text='State', anchor='center')  
  64. self.tvStudent.column('#5',width=10, anchor='center', stretch=True)  
  65. self.tvStudent.heading('#6', text='PhoneNumber', anchor='center')  
  66. self.tvStudent.column('#6', width=10, anchor='center', stretch=True)  
  67. self.tvStudent.heading('#7', text='Date of Birth', anchor='center')  
  68. self.tvStudent.column('#7', width=10, anchor='center', stretch=True)  
  69. #Scroll bars are set up below considering placement position(x&y) ,height and width of treeview widget  
  70. vsb= ttk.Scrollbar(self, orient=tk.VERTICAL,command=self.tvStudent.yview)  
  71. vsb.place(x=40 + 640 + 1, y=310, height=180 + 20)  
  72. self.tvStudent.configure(yscroll=vsb.set)  
  73. hsb = ttk.Scrollbar(self, orient=tk.HORIZONTAL, command=self.tvStudent.xview)  
  74. hsb.place(x=40 , y=310+200+1, width=620 + 20)  
  75. self.tvStudent.configure(xscroll=hsb.set)  
  76. self.tvStudent.bind("<<TreeviewSelect>>"self.show_selected_record)  
  77. self.lblTitle.place(x=280, y=30, height=27, width=300)  
  78. self.lblFName.place(x=175, y=70, height=23, width=100)  
  79. self.lblLName.place(x=175, y=100, height=23, width=100)  
  80. self.lblContactNo.place(x=171, y=129, height=23, width=104)  
  81. self.lblCity.place(x=210, y=158, height=23, width=65)  
  82. self.lblState.place(x=205, y=187, height=23, width=71)  
  83. self.lblDOB.place(x=148, y=217, height=23, width=128)  
  84. self.lblSelect.place(x=150, y=280, height=23, width=400)  
  85. self.lblSearch.place(x=174, y=560, height=23, width=134)  
  86. self.entFName.place(x=277, y=72, height=21, width=186)  
  87. self.entLName.place(x=277, y=100, height=21, width=186)  
  88. self.entContact.place(x=277, y=129, height=21, width=186)  
  89. self.entCity.place(x=277, y=158, height=21, width=186)  
  90. self.entState.place(x=278, y=188, height=21, width=186)  
  91. self.calDOB.place(x=278, y=218, height=21, width=186)  
  92. self.entSearch.place(x=310, y=560, height=21, width=186)  
  93. self.btn_register.place(x=290, y=245, height=25, width=76)  
  94. self.btn_update.place(x=370, y=245, height=25, width=76)  
  95. self.btn_delete.place(x=460, y=245, height=25, width=76)  
  96. self.btn_clear.place(x=548, y=245, height=25, width=76)  
  97. self.btn_show_all.place(x=630, y=245, height=25, width=76)  
  98. self.btn_search.place(x=498, y=558, height=26, width=60)  
  99. self.btn_exit.place(x=320, y=610, height=31, width=60)  
  100. self.tvStudent.place(x=40, y=310, height=200, width=640)  
  101. self.create_table()  
  102. self.load_student_data()  
  103. def clear_form(self):  
  104. self.entFName.delete(0, tk.END)  
  105. self.entLName.delete(0, tk.END)  
  106. self.entContact.delete(0, tk.END)  
  107. self.entCity.delete(0, tk.END)  
  108. self.entState.delete(0, tk.END)  
  109. self.calDOB.delete(0, tk.END)  
  110. def exit(self):  
  111. MsgBox = mb.askquestion('Exit Application''Are you sure you want to exit the application', icon='warning')  
  112. if MsgBox == 'yes':  
  113. self.destroy()  
  114. def delete_student_data(self):  
  115. MsgBox = mb.askquestion('Delete Record''Are you sure! you want to delete selected student record', icon='warning')  
  116. if MsgBox == 'yes':  
  117. if db_connection.is_connected() == False:  
  118. db_connection.connect()  
  119. db_cursor.execute("use Student"# Interact with Student Database  
  120. # deleteing selected student record  
  121. Delete = "delete from student_master where RollNo='%s'" % (roll_no)  
  122. db_cursor.execute(Delete)  
  123. db_connection.commit()  
  124. mb.showinfo("Information""Student Record Deleted Succssfully")  
  125. self.load_student_data()  
  126. self.entFName.delete(0, tk.END)  
  127. self.entLName.delete(0, tk.END)  
  128. self.entContact .delete(0, tk.END)  
  129. self.entCity.delete(0, tk.END)  
  130. self.entState.delete(0, tk.END)  
  131. self.calDOB.delete(0, tk.END)  
  132. def create_table(self):  
  133. if db_connection.is_connected() == False:  
  134. db_connection.connect()  
  135. # executing cursor with execute method and pass SQL query  
  136. db_cursor.execute("CREATE DATABASE IF NOT EXISTS Student"# Create a Database Named Student  
  137. db_cursor.execute("use Student"# Interact with Student Database  
  138. # creating required tables  
  139. db_cursor.execute("create table if not exists Student_master(Id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,rollno INT(15),fname VARCHAR(30),lname VARCHAR(30),city VARCHAR(20),state VARCHAR(30),mobileno VARCHAR(10),dob date)AUTO_INCREMENT=1")  
  140. db_connection.commit()  
  141. def register_student(self):  
  142. if db_connection.is_connected() == False:  
  143. db_connection.connect()  
  144. fname = self.entFName.get() # Retrieving entered first name  
  145. lname = self.entLName.get() # Retrieving entered last name  
  146. contact_no = self.entContact.get() # Retrieving entered contact number  
  147. city = self.entCity.get() # Retrieving entered city name  
  148. state = self.entState.get() # Retrieving entered state name  
  149. dob = self.calDOB.get() # Retrieving choosen date  
  150. # validating Entry Widgets  
  151. if fname == "":  
  152. mb.showinfo('Information'"Please Enter Firstname")  
  153. self.entFName.focus_set()  
  154. return  
  155. if lname == "":  
  156. mb.showinfo('Information'"Please Enter Lastname")  
  157. self.entLName.focus_set()  
  158. return  
  159. if contact_no == "":  
  160. mb.showinfo('Information'"Please Enter Contact Number")  
  161. self.entContact.focus_set()  
  162. return  
  163. if city == "":  
  164. mb.showinfo('Information'"Please Enter City Name")  
  165. self.entCity.focus_set()  
  166. return  
  167. if state == "":  
  168. mb.showinfo('Information'"Please Enter State Name")  
  169. self.entState.focus_set()  
  170. return  
  171. if dob == "":  
  172. mb.showinfo('Information'"Please Choose Date of Birth")  
  173. self.calDOB.focus_set()  
  174. return  
  175. # Inserting record into student_master table of student database  
  176. try:  
  177. rollno =int(self.fetch_max_roll_no())  
  178. print("New Student Id: " + str(rollno))  
  179. query2 = "INSERT INTO student_master (rollno, fname,lname,city,state,mobileno,dob) VALUES (%s, %s,%s, %s,%s, %s, %s)"  
  180. # implement query Sentence  
  181. db_cursor.execute(query2, (rollno, fname, lname, city, state, contact_no,dob))  
  182. mb.showinfo('Information'"Student Registration Successfully")  
  183. # Submit to database for execution  
  184. db_connection.commit()  
  185. self.load_student_data()  
  186. except mysql.connector.Error as err:  
  187. print(err)  
  188. # Rollback in case there is any error  
  189. db_connection.rollback()  
  190. mb.showinfo('Information'"Data insertion failed!!!")  
  191. finally:  
  192. db_connection.close()  
  193. def fetch_max_roll_no(self):  
  194. if db_connection.is_connected() == False:  
  195. db_connection.connect()  
  196. db_cursor.execute("use Student"# Interact with Student Database  
  197. rollno = 0  
  198. query1 = "SELECT rollno FROM student_master order by id DESC LIMIT 1"  
  199. # implement query Sentence  
  200. db_cursor.execute(query1) # Retrieving maximum student id no  
  201. print("No of Record Fetched:" + str(db_cursor.rowcount))  
  202. if db_cursor.rowcount == 0:  
  203. rollno = 1  
  204. else:  
  205. rows = db_cursor.fetchall()  
  206. for row in rows:  
  207. rollno = row[0]  
  208. rollno = rollno + 1  
  209. print("Max Student Id: " + str(rollno))  
  210. return rollno  
  211. def show_search_record(self):  
  212. if db_connection.is_connected() == False:  
  213. db_connection.connect()  
  214. s_roll_no = self.entSearch.get() # Retrieving entered first name  
  215. print(s_roll_no)  
  216. if s_roll_no == "":  
  217. mb.showinfo('Information'"Please Enter Student Roll")  
  218. self.entSearch.focus_set()  
  219. return  
  220. self.tvStudent.delete(*self.tvStudent.get_children()) # clears the treeview tvStudent  
  221. # Inserting record into student_master table of student database  
  222. db_cursor.execute("use Student"# Interact with Bank Database  
  223. sql = "SELECT rollno,fname,lname,city,state,mobileno,date_format(dob,'%d-%m-%Y') FROM student_master where rollno='" + s_roll_no + "'"  
  224. db_cursor.execute(sql)  
  225. total = db_cursor.rowcount  
  226. #if total ==0:  
  227. #mb.showinfo("Info", "Nothing To Display,Please add data")  
  228. #return  
  229. print("Total Data Entries:" + str(total))  
  230. rows = db_cursor.fetchall()  
  231. RollNo = ""  
  232. First_Name = ""  
  233. Last_Name = ""  
  234. City = ""  
  235. State = ""  
  236. Phone_Number = ""  
  237. DOB =""  
  238. for row in rows:  
  239. RollNo = row[0]  
  240. First_Name = row[1]  
  241. Last_Name = row[2]  
  242. City = row[3]  
  243. State = row[4]  
  244. Phone_Number = row[5]  
  245. DOB = row[6]  
  246. print( Phone_Number)  
  247. self.tvStudent.insert("", 'end', text=RollNo, values=(RollNo, First_Name, Last_Name, City, State, Phone_Number,DOB))  
  248. def show_selected_record(self, event):  
  249. self.clear_form()  
  250. for selection in self.tvStudent.selection():  
  251. item = self.tvStudent.item(selection)  
  252. global roll_no  
  253. roll_no,first_name,last_name,city,state,contact_no,dob = item["values"][0:7]  
  254. self.entFName.insert(0, first_name)  
  255. self.entLName.insert(0, last_name)  
  256. self.entCity.insert(0, city)  
  257. self.entState .insert(0, state)  
  258. self.entContact.insert(0, contact_no)  
  259. self.calDOB.insert(0, dob)  
  260. return roll_no  
  261. def update_student_data(self):  
  262. if db_connection.is_connected() == False:  
  263. db_connection.connect()  
  264. print("Updating")  
  265. db_cursor.execute("use Student"# Interact with Student Database  
  266. First_Name = self.entFName.get()  
  267. Last_Name = self.entLName.get()  
  268. Phone_Number = self.entContact.get()  
  269. City = self.entCity.get()  
  270. State = self.entState.get()  
  271. DOB = self.calDOB.get()  
  272. print( roll_no)  
  273. Update = "Update student_master set fname='%s', lname='%s', mobileno='%s', city='%s', state='%s', dob='%s' where rollno='%s'" % (  
  274. First_Name, Last_Name, Phone_Number, City, State,DOB, roll_no)  
  275. db_cursor.execute(Update)  
  276. db_connection.commit()  
  277. mb.showinfo("Info""Selected Student Record Updated Successfully ")  
  278. self.load_student_data()  
  279. def load_student_data(self):  
  280. if db_connection.is_connected() == False:  
  281. db_connection.connect()  
  282. self.calDOB.delete(0, tk.END)#clears the date entry widget  
  283. self.tvStudent.delete(*self.tvStudent.get_children()) # clears the treeview tvStudent  
  284. # Inserting record into student_master table of student database  
  285. db_cursor.execute("use Student"# Interact with Bank Database  
  286. sql = "SELECT rollno,fname,lname,city,state,mobileno,date_format(dob,'%d-%m-%Y') FROM student_master"  
  287. db_cursor.execute(sql)  
  288. total = db_cursor.rowcount  
  289. #if total ==0:  
  290. #mb.showinfo("Info", "Nothing To Display,Please add data")  
  291. #return  
  292. print("Total Data Entries:" + str(total))  
  293. rows = db_cursor.fetchall()  
  294. RollNo = ""  
  295. First_Name = ""  
  296. Last_Name = ""  
  297. City = ""  
  298. State = ""  
  299. Phone_Number = ""  
  300. DOB =""  
  301. for row in rows:  
  302. RollNo = row[0]  
  303. First_Name = row[1]  
  304. Last_Name = row[2]  
  305. City = row[3]  
  306. State = row[4]  
  307. Phone_Number = row[5]  
  308. DOB = row[6]  
  309. self.tvStudent.insert("", 'end', text=RollNo, values=(RollNo, First_Name, Last_Name, City, State, Phone_Number,DOB))  
  310. if __name__ == "__main__":  
  311. app = StudentApp()  
  312. app.mainloop()   
References
  1. Tkinter GUI application development cookbook by Alejandro Rodas de Paz of Packt Publications
  2. Home and Learn website https://www.homeandlearn.uk/index.html for learning python language.
  3. Stackoverflow.com