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