使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

作者 : 慕源网 本文共12944个字,预计阅读时间需要33分钟 发布时间: 2021-10-16 共264人阅读

介绍

这是一个简单的 CRUD(创建、检索、更新和删除)数据库记录桌面应用程序。它的名字是学生管理系统。将包含学生名字、姓氏、联系人编号、城市、州和出生日期的记录插入到学生数据库中名为 student_master 的表中。MySQL 数据库服务器。使用 Python 3.6、Tkinter、GUI builder 和 MySQL-connector。为了选择出生日期,使用了 tkcalendar 小部件。下面显示了从命令提示符安装 tkcalender 小部件的过程。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

Python 代码文件的名称是 StudentManager.py。执行文件后,应用程序窗口显示如下。在该窗口中,可以添加新的学生记录。MySQL 数据库表中所有添加的记录都显示在 Tkinter Treeview 小部件中,如下所示。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

插入学生记录

填写所有学生数据并从下拉日历小部件中选择出生日期,如下所示,然后单击注册按钮将记录添加到数据库中。

学生记录已成功插入并显示在下方。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

如果插入的记录状态名称拼写错误或者我们想要更改出生日期,则必须更新记录。请通过单击在显示的小部件中选择记录。所有数据都显示在上述框中以更新和删除记录。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

请编辑框中显示的字段并通过单击更新按钮更新它们。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

可以通过输入 roll no 并单击如上所示的搜索按钮来搜索学生记录。

要显示所有记录,请单击“全部显示”按钮。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

要删除记录,请在显示小部件中选择记录并单击删除按钮。

使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

StudentManager.py 代码

from tkcalendar import Calendar, DateEntry  
import tkinter as tk  
import tkinter.messagebox as mb  
import tkinter.ttk as ttk  
## Connecting to the database  
## importing 'mysql.connector' for connection to mysql database  
import mysql.connector  
## connecting to the database using 'connect()' method  
## it takes 3 required parameters 'host', 'user', 'password'  
#Please change user and password values to your  
#user and password values to connect to MySQL Database server   
db_connection = mysql.connector.connect(  
host="localhost",  
user="raichand70",  
password="1America")  
# creating database_cursor to perform SQL operation  
db_cursor = db_connection.cursor(buffered=True) # "buffered=True".makes db_cursor.row_count return actual number of records selected otherwise would return -1  
class StudentApp(tk.Tk):  
def __init__(self):  
super().__init__()  
self.title("Student Management System")  
self.geometry("800x650+351+174")  
self.lblTitle = tk.Label(self, text="Student Management System", font=("Helvetica", 16), bg="yellow", fg="green")  
self.lblFName = tk.Label(self, text="Enter FirstName:", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblLName = tk.Label(self, text="Enter LastName:", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblContactNo = tk.Label(self, text="Enter Contact No:", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblCity = tk.Label(self, text="Enter City:", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblState = tk.Label(self, text="Enter State:", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblDOB = tk.Label(self, text="Choose Date of Birth:", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblSelect = tk.Label(self, text="Please select one record below to update or delete", font=("Helvetica", 10), bg="blue", fg="yellow")  
self.lblSearch = tk.Label(self, text="Please Enter Roll No:",font=("Helvetica", 10), bg="blue", fg="yellow")  
self.entFName = tk.Entry(self)  
self.entLName = tk.Entry(self)  
self.entContact = tk.Entry(self)  
self.entCity = tk.Entry(self)  
self.entState = tk.Entry(self)  
self.calDOB = DateEntry(self, width=12, background='darkblue',  
foreground='white', borderwidth=2, year=1950,locale='en_US', date_pattern='y-mm-dd')  
#self.entDOB = tk.Entry(self)  
self.entSearch = tk.Entry(self)  
self.btn_register = tk.Button(self, text="Register", font=("Helvetica", 11), bg="yellow", fg="blue",  
command=self.register_student)  
self.btn_update = tk.Button(self,text="Update",font=("Helvetica",11),bg="yellow", fg="blue",command=self.update_student_data)  
self.btn_delete = tk.Button(self, text="Delete", font=("Helvetica", 11), bg="yellow", fg="blue",  
command=self.delete_student_data)  
self.btn_clear = tk.Button(self, text="Clear", font=("Helvetica", 11), bg="yellow", fg="blue",  
command=self.clear_form)  
self.btn_show_all = tk.Button(self, text="Show All", font=("Helvetica", 11), bg="yellow", fg="blue",  
command=self.load_student_data)  
self.btn_search = tk.Button(self, text="Search", font=("Helvetica", 11), bg="yellow", fg="blue",  
command=self.show_search_record)  
self.btn_exit = tk.Button(self, text="Exit", font=("Helvetica", 16), bg="yellow", fg="blue",command=self.exit)  
columns = ("#1", "#2", "#3", "#4", "#5", "#6", "#7")  
self.tvStudent= ttk.Treeview(self,show="headings",height="5", columns=columns)  
self.tvStudent.heading('#1', text='RollNo', anchor='center')  
self.tvStudent.column('#1', width=60, anchor='center', stretch=False)  
self.tvStudent.heading('#2', text='FirstName', anchor='center')  
self.tvStudent.column('#2', width=10, anchor='center', stretch=True)  
self.tvStudent.heading('#3', text='LastName', anchor='center')  
self.tvStudent.column('#3',width=10, anchor='center', stretch=True)  
self.tvStudent.heading('#4', text='City', anchor='center')  
self.tvStudent.column('#4',width=10, anchor='center', stretch=True)  
self.tvStudent.heading('#5', text='State', anchor='center')  
self.tvStudent.column('#5',width=10, anchor='center', stretch=True)  
self.tvStudent.heading('#6', text='PhoneNumber', anchor='center')  
self.tvStudent.column('#6', width=10, anchor='center', stretch=True)  
self.tvStudent.heading('#7', text='Date of Birth', anchor='center')  
self.tvStudent.column('#7', width=10, anchor='center', stretch=True)  
#Scroll bars are set up below considering placement position(x&y) ,height and width of treeview widget  
vsb= ttk.Scrollbar(self, orient=tk.VERTICAL,command=self.tvStudent.yview)  
vsb.place(x=40 + 640 + 1, y=310, height=180 + 20)  
self.tvStudent.configure(yscroll=vsb.set)  
hsb = ttk.Scrollbar(self, orient=tk.HORIZONTAL, command=self.tvStudent.xview)  
hsb.place(x=40 , y=310+200+1, width=620 + 20)  
self.tvStudent.configure(xscroll=hsb.set)  
self.tvStudent.bind("<<TreeviewSelect>>", self.show_selected_record)  
self.lblTitle.place(x=280, y=30, height=27, width=300)  
self.lblFName.place(x=175, y=70, height=23, width=100)  
self.lblLName.place(x=175, y=100, height=23, width=100)  
self.lblContactNo.place(x=171, y=129, height=23, width=104)  
self.lblCity.place(x=210, y=158, height=23, width=65)  
self.lblState.place(x=205, y=187, height=23, width=71)  
self.lblDOB.place(x=148, y=217, height=23, width=128)  
self.lblSelect.place(x=150, y=280, height=23, width=400)  
self.lblSearch.place(x=174, y=560, height=23, width=134)  
self.entFName.place(x=277, y=72, height=21, width=186)  
self.entLName.place(x=277, y=100, height=21, width=186)  
self.entContact.place(x=277, y=129, height=21, width=186)  
self.entCity.place(x=277, y=158, height=21, width=186)  
self.entState.place(x=278, y=188, height=21, width=186)  
self.calDOB.place(x=278, y=218, height=21, width=186)  
self.entSearch.place(x=310, y=560, height=21, width=186)  
self.btn_register.place(x=290, y=245, height=25, width=76)  
self.btn_update.place(x=370, y=245, height=25, width=76)  
self.btn_delete.place(x=460, y=245, height=25, width=76)  
self.btn_clear.place(x=548, y=245, height=25, width=76)  
self.btn_show_all.place(x=630, y=245, height=25, width=76)  
self.btn_search.place(x=498, y=558, height=26, width=60)  
self.btn_exit.place(x=320, y=610, height=31, width=60)  
self.tvStudent.place(x=40, y=310, height=200, width=640)  
self.create_table()  
self.load_student_data()  
def clear_form(self):  
self.entFName.delete(0, tk.END)  
self.entLName.delete(0, tk.END)  
self.entContact.delete(0, tk.END)  
self.entCity.delete(0, tk.END)  
self.entState.delete(0, tk.END)  
self.calDOB.delete(0, tk.END)  
def exit(self):  
MsgBox = mb.askquestion('Exit Application', 'Are you sure you want to exit the application', icon='warning')  
if MsgBox == 'yes':  
self.destroy()  
def delete_student_data(self):  
MsgBox = mb.askquestion('Delete Record', 'Are you sure! you want to delete selected student record', icon='warning')  
if MsgBox == 'yes':  
if db_connection.is_connected() == False:  
db_connection.connect()  
db_cursor.execute("use Student") # Interact with Student Database  
# deleteing selected student record  
Delete = "delete from student_master where RollNo='%s'" % (roll_no)  
db_cursor.execute(Delete)  
db_connection.commit()  
mb.showinfo("Information", "Student Record Deleted Succssfully")  
self.load_student_data()  
self.entFName.delete(0, tk.END)  
self.entLName.delete(0, tk.END)  
self.entContact .delete(0, tk.END)  
self.entCity.delete(0, tk.END)  
self.entState.delete(0, tk.END)  
self.calDOB.delete(0, tk.END)  
def create_table(self):  
if db_connection.is_connected() == False:  
db_connection.connect()  
# executing cursor with execute method and pass SQL query  
db_cursor.execute("CREATE DATABASE IF NOT EXISTS Student") # Create a Database Named Student  
db_cursor.execute("use Student") # Interact with Student Database  
# creating required tables  
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")  
db_connection.commit()  
def register_student(self):  
if db_connection.is_connected() == False:  
db_connection.connect()  
fname = self.entFName.get() # Retrieving entered first name  
lname = self.entLName.get() # Retrieving entered last name  
contact_no = self.entContact.get() # Retrieving entered contact number  
city = self.entCity.get() # Retrieving entered city name  
state = self.entState.get() # Retrieving entered state name  
dob = self.calDOB.get() # Retrieving choosen date  
# validating Entry Widgets  
if fname == "":  
mb.showinfo('Information', "Please Enter Firstname")  
self.entFName.focus_set()  
return  
if lname == "":  
mb.showinfo('Information', "Please Enter Lastname")  
self.entLName.focus_set()  
return  
if contact_no == "":  
mb.showinfo('Information', "Please Enter Contact Number")  
self.entContact.focus_set()  
return  
if city == "":  
mb.showinfo('Information', "Please Enter City Name")  
self.entCity.focus_set()  
return  
if state == "":  
mb.showinfo('Information', "Please Enter State Name")  
self.entState.focus_set()  
return  
if dob == "":  
mb.showinfo('Information', "Please Choose Date of Birth")  
self.calDOB.focus_set()  
return  
# Inserting record into student_master table of student database  
try:  
rollno =int(self.fetch_max_roll_no())  
print("New Student Id: " + str(rollno))  
query2 = "INSERT INTO student_master (rollno, fname,lname,city,state,mobileno,dob) VALUES (%s, %s,%s, %s,%s, %s, %s)"  
# implement query Sentence  
db_cursor.execute(query2, (rollno, fname, lname, city, state, contact_no,dob))  
mb.showinfo('Information', "Student Registration Successfully")  
# Submit to database for execution  
db_connection.commit()  
self.load_student_data()  
except mysql.connector.Error as err:  
print(err)  
# Rollback in case there is any error  
db_connection.rollback()  
mb.showinfo('Information', "Data insertion failed!!!")  
finally:  
db_connection.close()  
def fetch_max_roll_no(self):  
if db_connection.is_connected() == False:  
db_connection.connect()  
db_cursor.execute("use Student") # Interact with Student Database  
rollno = 0  
query1 = "SELECT rollno FROM student_master order by id DESC LIMIT 1"  
# implement query Sentence  
db_cursor.execute(query1) # Retrieving maximum student id no  
print("No of Record Fetched:" + str(db_cursor.rowcount))  
if db_cursor.rowcount == 0:  
rollno = 1  
else:  
rows = db_cursor.fetchall()  
for row in rows:  
rollno = row[0]  
rollno = rollno + 1  
print("Max Student Id: " + str(rollno))  
return rollno  
def show_search_record(self):  
if db_connection.is_connected() == False:  
db_connection.connect()  
s_roll_no = self.entSearch.get() # Retrieving entered first name  
print(s_roll_no)  
if s_roll_no == "":  
mb.showinfo('Information', "Please Enter Student Roll")  
self.entSearch.focus_set()  
return  
self.tvStudent.delete(*self.tvStudent.get_children()) # clears the treeview tvStudent  
# Inserting record into student_master table of student database  
db_cursor.execute("use Student") # Interact with Bank Database  
sql = "SELECT rollno,fname,lname,city,state,mobileno,date_format(dob,'%d-%m-%Y') FROM student_master where rollno='" + s_roll_no + "'"  
db_cursor.execute(sql)  
total = db_cursor.rowcount  
#if total ==0:  
#mb.showinfo("Info", "Nothing To Display,Please add data")  
#return  
print("Total Data Entries:" + str(total))  
rows = db_cursor.fetchall()  
RollNo = ""  
First_Name = ""  
Last_Name = ""  
City = ""  
State = ""  
Phone_Number = ""  
DOB =""  
for row in rows:  
RollNo = row[0]  
First_Name = row[1]  
Last_Name = row[2]  
City = row[3]  
State = row[4]  
Phone_Number = row[5]  
DOB = row[6]  
print( Phone_Number)  
self.tvStudent.insert("", 'end', text=RollNo, values=(RollNo, First_Name, Last_Name, City, State, Phone_Number,DOB))  
def show_selected_record(self, event):  
self.clear_form()  
for selection in self.tvStudent.selection():  
item = self.tvStudent.item(selection)  
global roll_no  
roll_no,first_name,last_name,city,state,contact_no,dob = item["values"][0:7]  
self.entFName.insert(0, first_name)  
self.entLName.insert(0, last_name)  
self.entCity.insert(0, city)  
self.entState .insert(0, state)  
self.entContact.insert(0, contact_no)  
self.calDOB.insert(0, dob)  
return roll_no  
def update_student_data(self):  
if db_connection.is_connected() == False:  
db_connection.connect()  
print("Updating")  
db_cursor.execute("use Student") # Interact with Student Database  
First_Name = self.entFName.get()  
Last_Name = self.entLName.get()  
Phone_Number = self.entContact.get()  
City = self.entCity.get()  
State = self.entState.get()  
DOB = self.calDOB.get()  
print( roll_no)  
Update = "Update student_master set fname='%s', lname='%s', mobileno='%s', city='%s', state='%s', dob='%s' where rollno='%s'" % (  
First_Name, Last_Name, Phone_Number, City, State,DOB, roll_no)  
db_cursor.execute(Update)  
db_connection.commit()  
mb.showinfo("Info", "Selected Student Record Updated Successfully ")  
self.load_student_data()  
def load_student_data(self):  
if db_connection.is_connected() == False:  
db_connection.connect()  
self.calDOB.delete(0, tk.END)#clears the date entry widget  
self.tvStudent.delete(*self.tvStudent.get_children()) # clears the treeview tvStudent  
# Inserting record into student_master table of student database  
db_cursor.execute("use Student") # Interact with Bank Database  
sql = "SELECT rollno,fname,lname,city,state,mobileno,date_format(dob,'%d-%m-%Y') FROM student_master"  
db_cursor.execute(sql)  
total = db_cursor.rowcount  
#if total ==0:  
#mb.showinfo("Info", "Nothing To Display,Please add data")  
#return  
print("Total Data Entries:" + str(total))  
rows = db_cursor.fetchall()  
RollNo = ""  
First_Name = ""  
Last_Name = ""  
City = ""  
State = ""  
Phone_Number = ""  
DOB =""  
for row in rows:  
RollNo = row[0]  
First_Name = row[1]  
Last_Name = row[2]  
City = row[3]  
State = row[4]  
Phone_Number = row[5]  
DOB = row[6]  
self.tvStudent.insert("", 'end', text=RollNo, values=(RollNo, First_Name, Last_Name, City, State, Phone_Number,DOB))  
if __name__ == "__main__":  
app = StudentApp()  
app.mainloop()   

慕源网 » 使用 Python3 和 MySQL 数据库服务器创建桌面应用程序(python开发学生管理系统)

常见问题FAQ

程序仅供学习研究,请勿用于非法用途,不得违反国家法律,否则后果自负,一切法律责任与本站无关。
请仔细阅读以上条款再购买,拍下即代表同意条款并遵守约定,谢谢大家支持理解!

发表评论

开通VIP 享更多特权,建议使用QQ登录