学校图书借阅管理系统(python + sql serve)数据库大作业

最近学校数据库原理结课,需要做一个大作业,还要求写图形化界面,现在分享我的源码,有很多不足之处我也懒的改了,给大家参考一下

具体要实现的功能为图书,读者信息的增删查改,借书还书触发器更改实时库存,超时罚款缴纳等下面是我的sql代码和python代码

图书信息(book_information):图书编号(book_id),图书名(book_name),作者(author),类别(book_category),出版社(publisher),图书总数   ( book_total) 
读者信息表(reader_information):读者编号(reader_id),姓名(reader_name),所在班级(reader_class),读者类别(reader_category),该类别读者可借阅册数(reader_borrowednumber),
图书库存表(book_inventory):图书编号(book_id),剩余库存数(book_surplus)
读者缴费表(reader_payment):读者编号(reader_id),待缴罚款(reader_fine)
读者借书表(reader_borrow):读者编号(borrow_id),所借书籍名称(borrow_bookname)借书时间(borrow_date)
读者还书表(reader_back):读者编号(back_id),所还书籍名称(back_bookname),还书时间(back_date)

CREATE TABLE book_information (
    book_id INT PRIMARY KEY, 
    book_name NVARCHAR(50) NOT NULL, 
    author NVARCHAR(100) NOT NULL, 
    book_category NVARCHAR(50), 
    publisher NVARCHAR(50),
    book_total int
    )--图书信息表

create table reader_information(
reader_id INT PRIMARY KEY, 
reader_name NVARCHAR(50) NOT NULL, 
reader_class int NOT NULL, 
reader_category NVARCHAR(50)not null, 
reader_borrowednumber int not null

)--读者信息表
create table book_inventory(
book_id INT PRIMARY KEY, 
book_surplus int NOT NULL, 
)--图书库存表

create table reader_payment(
reader_id varchar(20) PRIMARY KEY, 
reader_fine int NOT NULL, 
)--读者罚款表

create table reader_borrow(
borrow_id NVARCHAR(50), --为了比较成功运行触发器,borrow_id为借书人的名字
borrow_bookname NVARCHAR(50) NOT NULL, 
borrow_date date ,
)--读者借书表

create table reader_back(
back_id NVARCHAR(50), --为了比较成功运行触发器,borrow_id为借书人的名字
back_bookname NVARCHAR(50) NOT NULL, 
back_date date ,
)--读者还书表


create view book_select(书号,书名,总数,在馆册数)--图书信息查看视图
as select book_information.book_id,book_information.book_name,book_information.book_total,book_inventory.book_surplus
from book_inventory,book_information
where book_inventory.book_id=book_information.book_id

create trigger tri_jieshu on reader_borrow --借书触发器更改库存
for insert 
as
declare @id varchar(50)
select @id=borrow_id from inserted
update book_borrowing set book_surplus=book_surplus-1 
where book_id=@id


create trigger tri_huanshu on reader_back --还书触发器更改库存
for insert 
as
declare @id varchar(50)
select @id=back_id from inserted
update book_borrowing set book_surplus=book_surplus+1 
where book_id=@id

--借书还书存储过程返回借书日期和还书日期,返回值用列表的两个值相减得出超时天数计算罚款值,交罚款的时候先插入还书记录。获取还书时间,还书表和借书表连接创建视图,如果还书时间减借书日期大于7,选中这个人,
 create proc payment @id varchar(20),@name varchar(20)
as
    begin
        select borrow_date,back_date from reader_back,reader_borrow    
        where back_id=borrow_id and back_id=@id and borrow_bookname=back_bookname and (select datediff(day, borrow_date,back_date) FROM  reader_back,reader_borrow)>7
        

    end

--连接借书还书表创建视图
create view view_pay as select * from reader_back,reader_borrow where (borrow_id=back_id and borrow_bookname=back_bookname)
--创建存储过程通过传入参数id找到他的借书记录
create proc proc_pay (@id varchar(20))
as 
select back_id,back_bookname,back_date,borrow_date from view_pay
where back_id=@id 

exec proc_pay
@id='1'
import tkinter as tk
from tkinter import messagebox  # import this to fix messagebox error
from tkinter import StringVar
import pickle
import pymssql
import datetime

def conn():
    serverName = '127.0.0.1'    #目的主机ip地址
    userName = 'sa'            #SQL Server身份账号
    passWord = '1234567'        #SQL Server身份密码
    dbName = 'classwork'                #对应数据库名称
    # dbName = 'teach'                #对应数据库名称
    connect = pymssql.connect(server = serverName , database = dbName,charset='utf8') #Window默认身份验证建立连接

    return connect
conn=conn()
cursor=conn.cursor()

def menu():

    menu1 = tk.Tk()
    menu1.title('图书管理系统')
    menu1.geometry('700x400')
    l = tk.Label(menu1, text='你好!欢迎使用图书管理系统', bg='green', font=('Arial', 12), width=30, height=2)
    l.pack()
    var = tk.StringVar()    # 将label标签的内容设置为字符类型,用var来接收hit_me函数的传出内容用以显示在标签上

    function1 = tk.Button(menu1, text='1.图书信息管理', font=('Arial', 12), width=15, height=1, command=fun1,)
    function1.pack(expand=True)
    function2 = tk.Button(menu1, text='2.读者信息管理', font=('Arial', 12), width=15, height=1, command=fun2)
    function2.pack(expand=True)
    function3 = tk.Button(menu1, text='3.图书借阅管理', font=('Arial', 12), width=15, height=1, command=fun3)
    function3.pack(expand=True)
    function4 = tk.Button(menu1, text='4.超时罚款缴纳', font=('Arial', 12), width=15, height=1, command=fun4)
    function4.pack(expand=True)
    function5 = tk.Button(menu1, text='5.信息查询', font=('Arial', 12), width=15, height=1, command=fun5)
    function5.pack(expand=True)
    menu1.mainloop()

def fun1():
    root1 = tk.Tk()
    root1.title('图书信息管理')
    root1.geometry('700x400')
    root1_1 =tk.Button(root1,text='图书信息增加',font=('Arial', 12),width=15, height=1,command=lambda:root1_1_x(root1))
    root1_1.pack(expand=True)
    root1_2 =tk.Button(root1,text='图书信息删除',font=('Arial', 12),width=15, height=1,command=lambda:root1_2_x(root1))
    root1_2.pack(expand=True)
    root1_3 =tk.Button(root1,text='图书信息更新',font=('Arial', 12),width=15, height=1,command=lambda:root1_3_x(root1))
    root1_3.pack(expand=True)
    root1.mainloop()

def root1_1_x(self):
    self.destroy()


    def add_book():
         cursor.execute(("INSERT INTO book_information   VALUES ('" + entry_1.get() + "','" + entry_2.get() + "','" + entry_3.get() + "','" + entry_4.get() + "','" + entry_5.get() + "','" + entry_6.get() + "')"))
         conn.commit()
         conn.rollback()
         tk.messagebox.showinfo(title="提示", message="图书增加成功")
         root1_1_1.destroy()

    def back():
        root1_1_1.destroy()

    root1_1_1=tk.Tk()
    root1_1_1.title('图书信息增加')
    root1_1_1.geometry('700x400')
    tk.Label(root1_1_1,text='输入图书编号:',).place(x=220,y=50)
    entry_1=tk.Entry(root1_1_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root1_1_1,text='输入图书名称:',).place(x=220,y=80)
    entry_2=tk.Entry(root1_1_1,)
    entry_2.place(x=350, y=80)

    tk.Label(root1_1_1,text='输入作者名称:',).place(x=220,y=110)
    entry_3=tk.Entry(root1_1_1,)
    entry_3.place(x=350, y=110)

    tk.Label(root1_1_1,text='输入图书类别:',).place(x=220,y=140)
    entry_4=tk.Entry(root1_1_1,)
    entry_4.place(x=350, y=140)

    tk.Label(root1_1_1,text='输入图书出版社名称:',).place(x=220,y=170)
    entry_5=tk.Entry(root1_1_1,)
    entry_5.place(x=350, y=170)

    tk.Label(root1_1_1,text='请输入图书总数:',).place(x=220,y=200)
    entry_6=tk.Entry(root1_1_1,)
    entry_6.place(x=350, y=200)

    button1 =tk.Button(root1_1_1,text='确定',command=add_book).place(x=220,y=230)
    button2 =tk.Button(root1_1_1,text='返回',command=back).place(x=350,y=230)

    root1_1_1.mainloop()

def root1_2_x(self):
    self.destroy()
    root1_2_1=tk.Tk()
    root1_2_1.title('图书信息删除')
    root1_2_1.geometry('700x400')

    tk.Label(root1_2_1,text='输入图书编号:',).place(x=220,y=50)
    entry_1=tk.Entry(root1_2_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root1_2_1,text='输入图书名称:',).place(x=220,y=80)
    entry_2=tk.Entry(root1_2_1,)
    entry_2.place(x=350, y=80)

    def del_book():

        cursor.execute("select * from book_information where book_id='"+ entry_1.get() +"'and book_name = '" + entry_2.get() +"' ")
        a=cursor.fetchall()
        if len(a) !=0:
            cursor.execute(("delete from book_information where book_id='"+ entry_1.get() +"'and book_name = '" + entry_2.get() +"'"))
            conn.commit()
            conn.rollback()

            tk.messagebox.showinfo(title="提示", message="图书删除成功")
            root1_2_1.destroy()
        else:
            tk.messagebox.showerror(title='提示', message="未找到此书",)
            root1_2_1.destroy()



    def back():
        root1_2_1.destroy()
    button1 =tk.Button(root1_2_1,text='确定',command=del_book).place(x=220,y=230)
    button2 =tk.Button(root1_2_1,text='返回',command=back).place(x=350,y=230)

def root1_3_x(self):
    self.destroy()
    root1_3_1=tk.Tk()
    root1_3_1.title('图书信息更新')
    root1_3_1.geometry('700x400')
    root1=tk.Button(root1_3_1,text='以图书编号更改',command=lambda:root1_3_x_1(root1_3_1))
    root1.pack(expand=True)
    root2=tk.Button(root1_3_1,text='以图书名称更改',command=lambda:root1_3_x_2(root1_3_1))
    root2.pack(expand=True)

    def root1_3_x_1(self):#以图书编号更新
        self.destroy()
        root1_3_2 = tk.Toplevel()
        root1_3_2.title('以图书编号更改')
        root1_3_2.geometry('700x400')
        lab1=tk.Label(root1_3_2, text='输入图书编号:' )
        lab1.place(x=220, y=50)
        entry_a = tk.Entry(root1_3_2)
        entry_a.place(x=350, y=50)
        but1= tk.Button(root1_3_2, text='确定', command=lambda: root1_3_x_1_1(root1_3_2))
        but1.place(x=220,y=80)
        but2 =tk.Button(root1_3_2, text='返回', command=lambda: root1_3_x_1_2(root1_3_2))
        but2.place(x=400,y=80)
        def root1_3_x_1_1(self):#获取图书信息并进行更改写入数据库
            cursor.execute("select * from book_information where book_id='" + entry_a.get() + "' ")
            a = cursor.fetchall()
            conn.commit()
            conn.rollback()

            if len(a) == 0:

                return_value = tk.messagebox.showerror('提示','未找到此书')

                print(type(return_value), return_value)
                root1_3_2.destroy()

            else:#查询到图书信息并输出,用文本框获取输入的值

                root1_3_3 = tk.Tk()
                root1_3_3.title('以图书编号更改')
                root1_3_3.geometry('700x400')
                tk.Label(root1_3_3, text='查询到此图书信息为', ).place(x=150, y=80)
                tk.Label(root1_3_3, text='请在下列输入框中输入您要更新的图书信息', ).place(x=400, y=80)

                tk.Label(root1_3_3, text='图书编号', ).place(x=150, y=100)
                tk.Label(root1_3_3, text=a[0][0], ).place(x=250, y=100)
                tk.Label(root1_3_3, text=a[0][0], ).place(x=400, y=100)


                tk.Label(root1_3_3, text='图书名称', ).place(x=150, y=120)
                tk.Label(root1_3_3, text=a[0][1], ).place(x=250, y=120)
                entry_2 = tk.Entry(root1_3_3, )
                entry_2.place(x=400, y=120)

                tk.Label(root1_3_3, text='作者名称', ).place(x=150, y=140)
                tk.Label(root1_3_3, text=a[0][2], ).place(x=250, y=140)
                entry_3 = tk.Entry(root1_3_3, )
                entry_3.place(x=400, y=140)

                tk.Label(root1_3_3, text='图书类别', ).place(x=150, y=160)
                tk.Label(root1_3_3, text=a[0][3], ).place(x=250, y=160)
                entry_4 = tk.Entry(root1_3_3, )
                entry_4.place(x=400, y=160)

                tk.Label(root1_3_3, text='图书出版社名称', ).place(x=150, y=180)
                tk.Label(root1_3_3, text=a[0][4], ).place(x=250, y=180)
                entry_5 = tk.Entry(root1_3_3, )
                entry_5.place(x=400, y=180)

                tk.Label(root1_3_3, text='图书总数', ).place(x=150, y=200)
                tk.Label(root1_3_3, text=a[0][5], ).place(x=250, y=200)
                entry_6 = tk.Entry(root1_3_3, )
                entry_6.place(x=400, y=200)

                but3 = tk.Button(root1_3_3, text='确定', command=lambda: root1_3_x_1_2(root1_3_3)).place(x=250,y=250)
                but4 = tk.Button(root1_3_3, text='返回', command=lambda: back(root1_3_3)).place(x=400,y=250)
                def root1_3_x_1_2(self):#确认按钮执行数据的写入


                    #cursor.execute("select * from book_information where book_id='1'")
                    cursor.execute("update book_information set book_name ='" + entry_2.get() + "', author='" + entry_3.get() + "',book_category='" + entry_4.get() + "',publisher='" + entry_5.get() + "',book_total='" + entry_6.get() + "' where book_id='" + entry_a.get() + "' ")
                    #c = cursor.fetchall()
                    #print(c[0][1])
                    #cursor.execute("update book_information set book_name='%s',author='%s',book_category='%s',publisher='%s',book_total='%s' where book_id ='1' ") %(entry_2.get(),entry_3.get(),entry_4.get(),entry_5.get(),entry_6.get())
                    conn.commit()
                    conn.rollback()
                    self.destroy()
                    tk.messagebox.showinfo(title="提示", message="图书更新成功")
                def back(self):
                    self.destroy()


                root1_3_3.mainloop()





        def root1_3_x_1_2(self):#返回按钮
            self.destroy()

    def root1_3_x_2(self):#以图书名称更新图书信息
        self.destroy()
        root1_3_3 = tk.Toplevel()
        root1_3_3.title('以图书名称更改')
        root1_3_3.geometry('700x400')
        lab1 = tk.Label(root1_3_3, text='输入图书名称:')
        lab1.place(x=220, y=50)
        entry_a = tk.Entry(root1_3_3)
        entry_a.place(x=350, y=50)

        print(entry_a.get())

        but1 = tk.Button(root1_3_3, text='确定', command=lambda: root1_3_x_1_2(root1_3_3))
        but1.place(x=220, y=80)
        but2 = tk.Button(root1_3_3, text='返回', command=lambda: back(root1_3_3))
        but2.place(x=400, y=80)

        def back(self):
            self.destroy()
        def root1_3_x_1_2(self):#获取读者信息并进行更改写入数据库
            cursor.execute("select * from book_information where book_name='" + entry_a.get() + "' ")
            a = cursor.fetchall()
            conn.commit()
            conn.rollback()

            if len(a) == 0:

                return_value = tk.messagebox.showerror('提示','未找到此书')

                print(type(return_value), return_value)
                root1_3_3.destroy()

            else:#查询到图书信息并输出,用文本框获取输入的值

                root1_3_4 = tk.Tk()
                root1_3_4.title('以图书名称更改')
                root1_3_4.geometry('700x400')
                tk.Label(root1_3_4, text='查询到此图书信息为', ).place(x=150, y=80)
                tk.Label(root1_3_4, text='请在下列输入框中输入您要更新的图书信息', ).place(x=400, y=80)

                tk.Label(root1_3_4, text='图书名称', ).place(x=150, y=100)
                tk.Label(root1_3_4, text=a[0][0], ).place(x=250, y=100)
                tk.Label(root1_3_4, text=a[0][0], ).place(x=400, y=100)


                tk.Label(root1_3_4, text='图书编号', ).place(x=150, y=120)
                tk.Label(root1_3_4, text=a[0][1], ).place(x=250, y=120)
                entry_2 = tk.Entry(root1_3_4, )
                entry_2.place(x=400, y=120)

                tk.Label(root1_3_4, text='作者名称', ).place(x=150, y=140)
                tk.Label(root1_3_4, text=a[0][2], ).place(x=250, y=140)
                entry_3 = tk.Entry(root1_3_4, )
                entry_3.place(x=400, y=140)

                tk.Label(root1_3_4, text='图书类别', ).place(x=150, y=160)
                tk.Label(root1_3_4, text=a[0][3], ).place(x=250, y=160)
                entry_4 = tk.Entry(root1_3_4, )
                entry_4.place(x=400, y=160)

                tk.Label(root1_3_4, text='图书出版社名称', ).place(x=150, y=180)
                tk.Label(root1_3_4, text=a[0][4], ).place(x=250, y=180)
                entry_5 = tk.Entry(root1_3_4, )
                entry_5.place(x=400, y=180)

                tk.Label(root1_3_4, text='图书总数', ).place(x=150, y=200)
                tk.Label(root1_3_4, text=a[0][5], ).place(x=250, y=200)
                entry_6 = tk.Entry(root1_3_4, )
                entry_6.place(x=400, y=200)

                but3 = tk.Button(root1_3_4, text='确定', command=lambda: root1_3_x_1_3(root1_3_4)).place(x=250,y=250)
                but4 = tk.Button(root1_3_4, text='返回', command=lambda: back(root1_3_4)).place(x=400,y=250)
                def root1_3_x_1_3(self):#确认按钮执行数据的写入


                    #cursor.execute("select * from book_information where book_id='1'")
                    cursor.execute("update book_information set book_id ='" + entry_2.get() + "', author='" + entry_3.get() + "',book_category='" + entry_4.get() + "',publisher='" + entry_5.get() + "',book_total='" + entry_6.get() + "' where book_name='" + entry_a.get() + "' ")
                    #c = cursor.fetchall()
                    #print(c[0][1])
                    #cursor.execute("update book_information set book_name='%s',author='%s',book_category='%s',publisher='%s',book_total='%s' where book_id ='1' ") %(entry_2.get(),entry_3.get(),entry_4.get(),entry_5.get(),entry_6.get())
                    conn.commit()
                    conn.rollback()
                    self.destroy()
                    tk.messagebox.showinfo(title="提示", message="图书更新成功")
                    root1_3_3.destroy()



                root1_3_4.mainloop()

def fun2():
    root2 = tk.Tk()
    root2.title('读者信息管理')
    root2.geometry('700x400')
    root2_1 =tk.Button(root2,text='读者信息增加',font=('Arial', 12),width=15, height=1,command=lambda:root2_1_x(root2))
    root2_1.pack(expand=True)
    root2_2 =tk.Button(root2,text='读者信息删除',font=('Arial', 12),width=15, height=1,command=lambda:root2_2_x(root2))
    root2_2.pack(expand=True)
    root2_3 =tk.Button(root2,text='读者信息更新',font=('Arial', 12),width=15, height=1,command=lambda:root2_3_x(root2))
    root2_3.pack(expand=True)

def root2_1_x(self):#增加读者信息界面
    self.destroy()


    def add_reader():
         cursor.execute(("INSERT INTO reader_information   VALUES ('" + entry_1.get() + "','" + entry_2.get() + "','" + entry_3.get() + "','" + entry_4.get() + "','" + entry_5.get() + "')"))
         conn.commit()
         conn.rollback()
         tk.messagebox.showinfo(title="提示", message="读者增加成功")
         root2_1_1.destroy()

    def back():
        root2_1_1.destroy()

    root2_1_1=tk.Tk()
    root2_1_1.title('读者信息增加')
    root2_1_1.geometry('700x400')
    tk.Label(root2_1_1,text='输入读者编号:',).place(x=220,y=50)
    entry_1=tk.Entry(root2_1_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root2_1_1,text='输入读者名称:',).place(x=220,y=80)
    entry_2=tk.Entry(root2_1_1,)
    entry_2.place(x=350, y=80)

    tk.Label(root2_1_1,text='输入读者班级:',).place(x=220,y=110)
    entry_3=tk.Entry(root2_1_1,)
    entry_3.place(x=350, y=110)

    tk.Label(root2_1_1,text='输入读者类别:',).place(x=220,y=140)
    entry_4=tk.Entry(root2_1_1,)
    entry_4.place(x=350, y=140)

    tk.Label(root2_1_1,text='输入可借阅最大书籍数:',).place(x=220,y=170)
    entry_5=tk.Entry(root2_1_1,)
    entry_5.place(x=350, y=170)






    button1 =tk.Button(root2_1_1,text='确定',command=add_reader).place(x=220,y=230)
    button2 =tk.Button(root2_1_1,text='返回',command=back).place(x=350,y=230)

    root2_1_1.mainloop()

def root2_2_x(self):#删除读者信息界面
    self.destroy()
    root2_2_1=tk.Tk()
    root2_2_1.title('读者信息删除')
    root2_2_1.geometry('700x400')

    tk.Label(root2_2_1,text='输入读者编号:',).place(x=220,y=50)
    entry_1=tk.Entry(root2_2_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root2_2_1,text='输入读者名称:',).place(x=220,y=80)
    entry_2=tk.Entry(root2_2_1,)
    entry_2.place(x=350, y=80)

    def del_reader():

        cursor.execute("select * from reader_information where reader_id='"+ entry_1.get() +"'and reader_name = '" + entry_2.get() +"' ")
        a=cursor.fetchall()
        if len(a) !=0:
            cursor.execute(("delete from reader_information where reader_id='"+ entry_1.get() +"'and reader_name = '" + entry_2.get() +"'"))
            conn.commit()
            conn.rollback()

            tk.messagebox.showinfo(title="提示", message="读者删除成功")
            root2_2_1.destroy()
        else:
            tk.messagebox.showerror(title='提示', message="未找到此读者")
            root2_2_1.destroy()



    def back():
        root2_2_1.destroy()
    button1 =tk.Button(root2_2_1,text='确定',command=del_reader).place(x=220,y=230)
    button2 =tk.Button(root2_2_1,text='返回',command=back).place(x=350,y=230)

def root2_3_x(self):#更新读者信息界面
    self.destroy()
    root2_3_1 = tk.Tk()
    root2_3_1.title('读者信息更新')
    root2_3_1.geometry('700x400')
    root1 = tk.Button(root2_3_1, text='以读者编号更改', command=lambda: root2_3_x_1(root2_3_1))
    root1.pack(expand=True)
    root2 = tk.Button(root2_3_1, text='以读者名称更改', command=lambda: root2_3_x_2(root2_3_1))
    root2.pack(expand=True)

    def root2_3_x_1(self):  # 以读者编号更新
        self.destroy()
        root2_3_2 = tk.Toplevel()
        root2_3_2.title('以读者编号更改')
        root2_3_2.geometry('700x400')
        lab1 = tk.Label(root2_3_2, text='输入读者编号:')

        lab1.place(x=220, y=50)
        entry_a = tk.Entry(root2_3_2)
        entry_a.place(x=350, y=50)
        but1 = tk.Button(root2_3_2, text='确定', command=lambda: root2_3_x_1_1(root2_3_2))
        but1.place(x=220, y=80)
        but2 = tk.Button(root2_3_2, text='返回', command=lambda: root2_3_x_1_2(root2_3_2))
        but2.place(x=400, y=80)

        def root2_3_x_1_1(self):  # 获取读者信息并进行更改写入数据库
            cursor.execute("select * from reader_information where reader_id='" + entry_a.get() + "' ")
            a = cursor.fetchall()
            conn.commit()
            conn.rollback()
            self.destroy()

            if len(a) == 0:

                return_value = tk.messagebox.showerror('提示', '未找到读者')

                print(type(return_value), return_value)
                root2_3_2.destroy()

            else:  # 查询到读者信息并输出,用文本框获取输入的值

                root2_3_3 = tk.Tk()
                root2_3_3.title('以读者编号更改')
                root2_3_3.geometry('700x400')
                tk.Label(root2_3_3, text='查询到此读者信息为', ).place(x=150, y=80)
                tk.Label(root2_3_3, text='请在下列输入框中输入您要更新的读者信息', ).place(x=400, y=80)

                tk.Label(root2_3_3, text='读者编号', ).place(x=150, y=100)
                tk.Label(root2_3_3, text=a[0][0], ).place(x=250, y=100)
                tk.Label(root2_3_3, text=a[0][0], ).place(x=400, y=100)

                tk.Label(root2_3_3, text='读者名称', ).place(x=150, y=120)
                tk.Label(root2_3_3, text=a[0][1], ).place(x=250, y=120)
                entry_2 = tk.Entry(root2_3_3, )
                entry_2.place(x=400, y=120)

                tk.Label(root2_3_3, text='读者班级', ).place(x=150, y=140)
                tk.Label(root2_3_3, text=a[0][2], ).place(x=250, y=140)
                entry_3 = tk.Entry(root2_3_3, )
                entry_3.place(x=400, y=140)

                tk.Label(root2_3_3, text='读者类别', ).place(x=150, y=160)
                tk.Label(root2_3_3, text=a[0][3], ).place(x=250, y=160)
                entry_4 = tk.Entry(root2_3_3, )
                entry_4.place(x=400, y=160)

                tk.Label(root2_3_3, text='读者最大借阅数', ).place(x=150, y=180)
                tk.Label(root2_3_3, text=a[0][4], ).place(x=250, y=180)
                entry_5 = tk.Entry(root2_3_3, )
                entry_5.place(x=400, y=180)


                but3 = tk.Button(root2_3_3, text='确定', command=lambda: root2_3_x_1_2(root2_3_3)).place(x=250, y=250)
                but4 = tk.Button(root2_3_3, text='返回', command=lambda: back(root2_3_3)).place(x=400, y=250)

                def root2_3_x_1_2(self):  # 确认按钮执行数据的写入

                    # cursor.execute("select * from book_information where book_id='1'")
                    cursor.execute("update reader_information set reader_name ='" + entry_2.get() + "', reader_class='" + entry_3.get() + "',reader_category='" + entry_4.get() + "',reader_borrowednumber='" + entry_5.get() + "' where reader_id='" + entry_a.get() + "' ")
                    # c = cursor.fetchall()
                    # print(c[0][1])
                    # cursor.execute("update book_information set book_name='%s',author='%s',book_category='%s',publisher='%s',book_total='%s' where book_id ='1' ") %(entry_2.get(),entry_3.get(),entry_4.get(),entry_5.get(),entry_6.get())
                    conn.commit()
                    conn.rollback()
                    #self.destroy()
                    tk.messagebox.showinfo(title="提示", message="读者信息更新成功")

                def back(self):
                    self.destroy()

                root2_3_3.mainloop()

        def root2_3_x_1_2(self):  # 返回按钮
            self.destroy()

    def root2_3_x_2(self):  # 以读者名称更新图书信息
        self.destroy()
        root2_3_3 = tk.Toplevel()
        root2_3_3.title('以读者名称更改')
        root2_3_3.geometry('700x400')
        lab1 = tk.Label(root2_3_3, text='输入读者名称:')
        lab1.place(x=220, y=50)
        entry_a = tk.Entry(root2_3_3)
        entry_a.place(x=350, y=50)

        but1 = tk.Button(root2_3_3, text='确定', command=lambda: root2_3_x_1_2(root2_3_3))
        but1.place(x=220, y=80)
        but2 = tk.Button(root2_3_3, text='返回', command=lambda: back(root2_3_3))
        but2.place(x=400, y=80)

        def back(self):
            self.destroy()

        def root2_3_x_1_2(self):  # 获取读者信息并进行更改写入数据库
            cursor.execute("select * from reader_information where reader_name='" + entry_a.get() + "' ")
            a = cursor.fetchall()
            conn.commit()
            conn.rollback()

            if len(a) == 0:

                return_value = tk.messagebox.showerror('提示', '未找到此读者')

                print(type(return_value), return_value)
                root2_3_3.destroy()

            else:  # 查询到图书信息并输出,用文本框获取输入的值

                root2_3_4 = tk.Tk()
                root2_3_4.title('以读者名称更改')
                root2_3_4.geometry('700x400')
                tk.Label(root2_3_4, text='查询到此读者信息为', ).place(x=150, y=80)
                tk.Label(root2_3_4, text='请在下列输入框中输入您要更新的读者信息', ).place(x=400, y=80)

                tk.Label(root2_3_4, text='读者姓名', ).place(x=150, y=100)
                tk.Label(root2_3_4, text=a[0][0], ).place(x=250, y=100)
                tk.Label(root2_3_4, text=a[0][0], ).place(x=400, y=100)

                tk.Label(root2_3_4, text='读者编号', ).place(x=150, y=120)
                tk.Label(root2_3_4, text=a[0][1], ).place(x=250, y=120)
                entry_2 = tk.Entry(root2_3_4, )
                entry_2.place(x=400, y=120)

                tk.Label(root2_3_4, text='读者班级', ).place(x=150, y=140)
                tk.Label(root2_3_4, text=a[0][2], ).place(x=250, y=140)
                entry_3 = tk.Entry(root2_3_4, )
                entry_3.place(x=400, y=140)

                tk.Label(root2_3_4, text='读者类别', ).place(x=150, y=160)
                tk.Label(root2_3_4, text=a[0][3], ).place(x=250, y=160)
                entry_4 = tk.Entry(root2_3_4, )
                entry_4.place(x=400, y=160)

                tk.Label(root2_3_4, text='读者最大借阅数', ).place(x=150, y=180)
                tk.Label(root2_3_4, text=a[0][4], ).place(x=250, y=180)
                entry_5 = tk.Entry(root2_3_4, )
                entry_5.place(x=400, y=180)


                but3 = tk.Button(root2_3_4, text='确定', command=lambda: root2_3_x_1_3(root2_3_4)).place(x=250, y=250)
                but4 = tk.Button(root2_3_4, text='返回', command=lambda: back(root2_3_4)).place(x=400, y=250)

                def root2_3_x_1_3(self):  # 确认按钮执行数据的写入reader_borrowednumber

                    # cursor.execute("select * from book_information where book_id='1'")
                    cursor.execute(
                        "update reader_information set reader_id ='" + entry_2.get() + "', reader_class='" + entry_3.get() + "',reader_category='" + entry_4.get() + "',reader_borrowednumber='" + entry_5.get() + "' where reader_name='" + entry_a.get() + "' ")
                    # c = cursor.fetchall()
                    # print(c[0][1])
                    # cursor.execute("update book_information set book_name='%s',author='%s',book_category='%s',publisher='%s',book_total='%s' where book_id ='1' ") %(entry_2.get(),entry_3.get(),entry_4.get(),entry_5.get(),entry_6.get())
                    conn.commit()
                    conn.rollback()
                    self.destroy()
                    tk.messagebox.showinfo(title="提示", message="读者更新成功")
                    root2_3_3.destroy()

                root2_3_4.mainloop()

def fun3():
    root3 = tk.Tk()
    root3.title('图书借阅管理')
    root3.geometry('700x400')
    root3_1 =tk.Button(root3,text='图书借阅',font=('Arial', 12),width=15, height=1,command=lambda:root3_1_x(root3))
    root3_1.pack(expand=True)
    root3_2 =tk.Button(root3,text='图书归还',font=('Arial', 12),width=15, height=1,command=lambda:root3_2_x(root3))
    root3_2.pack(expand=True)
    root3.mainloop()

def root3_1_x(self):#借书功能
    self.destroy()
    root3_1_1=tk.Tk()
    root3_1_1.title('图书借阅')
    root3_1_1.geometry('700x400')

    def borrow_book():
        cursor.execute("insert into reader_borrow values ('%s','%s','%s')" % (entry_1.get(),entry_2.get(),entry_3.get()))
        conn.commit()
        conn.rollback()
        tk.messagebox.showinfo(title="提示", message="图书借阅成功")
        root3_1_1.destroy()
    def back():
        root3_1_1.destroy()
        return fun3()



    tk.Label(root3_1_1,text='输入读者id:',).place(x=220,y=50)
    entry_1=tk.Entry(root3_1_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root3_1_1,text='输入所借阅书名:',).place(x=220,y=100)
    entry_2=tk.Entry(root3_1_1,)
    entry_2.place(x=350, y=100)

    tk.Label(root3_1_1,text='输入借阅日期:',).place(x=220,y=150)
    entry_3=tk.Entry(root3_1_1,)
    entry_3.insert(0,day_now)
    entry_3.place(x=350, y=150)


    button1 =tk.Button(root3_1_1,text='确定',command=borrow_book).place(x=220,y=230)
    button2 =tk.Button(root3_1_1,text='返回',command=back).place(x=350,y=230)

    root3_1_1.mainloop()

def root3_2_x(self):#还书功能
    self.destroy()
    root3_2_1=tk.Tk()
    root3_2_1.title('图书归还')
    root3_2_1.geometry('700x400')

    def back_book():
        cursor.execute("insert into reader_back values ('%s','%s','%s')" % (entry_1.get(),entry_2.get(),entry_3.get()))
        conn.commit()
        conn.rollback()
        tk.messagebox.showinfo(title="提示", message="图书归还成功")
        root3_2_1.destroy()
    def back():
        root3_2_1.destroy()
        return fun3()



    tk.Label(root3_2_1,text='输入读者id:',).place(x=220,y=50)
    entry_1=tk.Entry(root3_2_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root3_2_1,text='输入所归还书名:',).place(x=220,y=100)
    entry_2=tk.Entry(root3_2_1,)
    entry_2.place(x=350, y=100)

    tk.Label(root3_2_1,text='输入归还日期:',).place(x=220,y=150)
    entry_3=tk.Entry(root3_2_1,)
    entry_3.insert(0,day_now)
    entry_3.place(x=350, y=150)


    button1 =tk.Button(root3_2_1,text='确定',command=back_book).place(x=220,y=230)
    button2 =tk.Button(root3_2_1,text='返回',command=back).place(x=350,y=230)

    root3_2_1.mainloop()

def fun4():
    root4_1=tk.Toplevel()
    root4_1.title('罚款查询')
    root4_1.geometry('700x400')

    def select():

        def box1():
            cursor.execute("delete from reader_payment where reader_id = '"+entry_1.get()+"'")
            conn.commit()
            conn.rollback()
            tk.messagebox.showinfo(title="提示", message="缴纳成功")
            root4_1.destroy()
            root4_1_1.destroy()


        def back1(self):
            self.destroy()
            cursor.execute("delete from reader_payment where reader_id = '"+entry_1.get()+"'")
            conn.commit()
            conn.rollback()
            #return fun4()

        cursor.execute("exec proc_pay @id='"+ entry_1.get() +"'")
        a = cursor.fetchall()
        conn.commit()
        conn.rollback()
        if len(a)!=0:
            sum = 0
            for i in range(len(a)):
                x=a[i][2]
                y=a[i][3]
                x1 =x.strftime("%Y-%m-%d")
                y1=  y.strftime("%Y-%m-%d")
                q=x1.split('-',2)[0]
                w = x1.split('-', 2)[1]
                e = x1.split('-', 2)[2]
                r=y1.split('-',2)[0]
                t = y1.split('-', 2)[1]
                u = y1.split('-', 2)[2]
                days=365*(int(q)-int(r))+31*(int(w)-int(t))+int(e)-int(u)
                sum = sum + (1.5 * days)
                cursor.execute("insert into reader_payment values ('%s','%d')" % (entry_1.get(),sum))
                conn.commit()
                conn.rollback()



            root4_1_1=tk.Tk()
            root4_1_1.title('罚款查询')
            root4_1_1.geometry('700x400')
            tk.Label(root4_1_1, text="{}待缴纳罚款:".format(entry_2.get()) ).place(x=160, y=50)
            tk.Label(root4_1_1, text="{}元".format(sum)).place(x=300, y=50)


            button1 = tk.Button(root4_1_1, text='确定缴纳', command=box1).place(x=160, y=230)
            button2 = tk.Button(root4_1_1, text='返回', command=lambda :back1(root4_1_1)).place(x=300, y=230)

        else:
            tk.messagebox.showinfo(title="提示", message="未查询到此人有待缴罚款")

    def back():
        root4_1.destroy()


    tk.Label(root4_1,text='输入读者id:',).place(x=220,y=50)
    entry_1=tk.Entry(root4_1,)
    entry_1.place(x=350, y=50)

    tk.Label(root4_1,text='输入读者姓名:',).place(x=220,y=100)
    entry_2=tk.Entry(root4_1,)
    entry_2.place(x=350, y=100)

    button1 =tk.Button(root4_1,text='确定',command=select).place(x=220,y=230)
    button2 =tk.Button(root4_1,text='返回',command=back).place(x=350,y=230)

    root4_1.mainloop()

def fun5():

    root5 = tk.Tk()
    root5.title('信息查询')
    root5.geometry('700x400')
    function1 = tk.Button(root5, text='1.图书信息查询', font=('Arial', 12), width=15, height=1, command=lambda :root5_1(root5),)
    function1.pack(expand=True)
    function2 = tk.Button(root5, text='2.读者信息查询', font=('Arial', 12), width=15, height=1, command=lambda:root5_2(root5))
    function2.pack(expand=True)

def root5_1(self):
    self.destroy()
    def back(self):
        self.destroy()


    def select_book_1():
        root5_1_1.update()
        root5_1_1.destroy()
        sql="select * from book_select"
        cursor.execute(sql)
        a=cursor.fetchall()
        print("--------------------作品信息--------------------")
        print('图书编号: 图书名称:  图书总数:  在馆册数: ')
        root5_1_1_1 = tk.Tk()
        root5_1_1_1.title('图书信息查询')
        root5_1_1_1.geometry('700x400')
        root5_1_1_1.configure()
        l = tk.Label(root5_1_1_1, text='图书信息如下')
        l.pack()
        l1 = tk.Label(root5_1_1_1, text='图书编号:')
        l1.place(x=20, y=40, width=90, height=20)
        l2 = tk.Label(root5_1_1_1, text='图书名称:')
        l2.place(x=135, y=40, width=90, height=20)
        l3 = tk.Label(root5_1_1_1, text='图书总数:')
        l3.place(x=270, y=40, width=90, height=20)
        l4 = tk.Label(root5_1_1_1, text='在馆册数:')
        l4.place(x=405, y=40, width=90, height=20)
        j=1
        t=0
        ex=0
        for i in range(len(a)):
            ex=ex+1
            t=t+1
            if t%5==0 or t==len(a):
                break

        for i in range(t-ex,t):
            y=j*45+40
            l11 = tk.Label(root5_1_1_1, text=('{}').format(a[i][0]))
            l11.place(x=20, y=y, width=90, height=20)
            l22 = tk.Label(root5_1_1_1, text=('{}').format(a[i][1]))
            l22.place(x=135, y=y, width=90, height=20)
            l33 = tk.Label(root5_1_1_1, text=('{}').format(a[i][2]))
            l33.place(x=270, y=y, width=90, height=20)
            l44 = tk.Label(root5_1_1_1,text=('{}').format(a[i][3]))
            l44.place(x=405, y=y, width=90, height=20)
            j=j+1
        if t!=len(a):
            buttonOk = tk.Button(root5_1_1_1, font=('Arial', 11), text='点击返回主页面',
                                      command=lambda: back(root5_1_1_1))
            buttonOk.place(x=250, y=350, width=160, height=40)
            buttonOk = tk.Button(root5_1_1_1, font=('Arial', 11), text='下一页',
                                      command=lambda: back(root5_1_1_1,'a3b1','we'))
            buttonOk.place(x=300, y=300, width=60, height=40)
        else:
            t=0
            buttonOk = tk.Button(root5_1_1_1,  font=('Arial', 11), text='已是最后一页,点击返回主页面',
                                          command=lambda: back(root5_1_1_1))
            buttonOk.place(x=180, y=300, width=250, height=40)
        root5_1_1_1.mainloop()


    root5_1_1 = tk.Tk()
    root5_1_1.title('图书信息查询')
    root5_1_1.geometry('700x400')
    select_book_1()
    root5_1_1.mainloop()

def root5_2(self):
    self.destroy()
    def back(self):
        self.destroy()


    def select_book_1():
        root5_1_2.update()
        root5_1_2.destroy()
        sql="select * from reader_information"
        cursor.execute(sql)
        a=cursor.fetchall()
        print("--------------------作品信息--------------------")
        print('读者编号: 读者名称:  读者班级:  读者类别:  最大借阅数: ')
        root5_1_2_1 = tk.Tk()
        root5_1_2_1.title('读者信息查询')
        root5_1_2_1.geometry('700x400')
        root5_1_2_1.configure()
        l = tk.Label(root5_1_2_1, text='读者信息如下')
        l.pack()
        l1 = tk.Label(root5_1_2_1, text='读者编号:')
        l1.place(x=20, y=40, width=90, height=20)
        l2 = tk.Label(root5_1_2_1, text='读者名称:')
        l2.place(x=135, y=40, width=90, height=20)
        l3 = tk.Label(root5_1_2_1, text='读者班级:')
        l3.place(x=270, y=40, width=90, height=20)
        l4 = tk.Label(root5_1_2_1, text='读者类别:')
        l4.place(x=405, y=40, width=90, height=20)
        l5 = tk.Label(root5_1_2_1, text='最大借阅数:')
        l5.place(x=540, y=40, width=90, height=20)
        j=1
        t=0
        ex=0
        for i in range(len(a)):
            ex=ex+1
            t=t+1
            if t%5==0 or t==len(a):
                break

        for i in range(t-ex,t):
            y=j*45+40
            l11 = tk.Label(root5_1_2_1, text=('{}').format(a[i][0]))
            l11.place(x=20, y=y, width=90, height=20)
            l22 = tk.Label(root5_1_2_1, text=('{}').format(a[i][1]))
            l22.place(x=135, y=y, width=90, height=20)
            l33 = tk.Label(root5_1_2_1, text=('{}').format(a[i][2]))
            l33.place(x=270, y=y, width=90, height=20)
            l44 = tk.Label(root5_1_2_1,text=('{}').format(a[i][3]))
            l44.place(x=405, y=y, width=90, height=20)
            l55 = tk.Label(root5_1_2_1,text=('{}').format(a[i][4]))
            l55.place(x=540, y=y, width=90, height=20)
            j=j+1
        if t!=len(a):
            buttonOk = tk.Button(root5_1_2_1, font=('Arial', 11), text='点击返回主页面',
                                      command=lambda: back(root5_1_2_1))
            buttonOk.place(x=250, y=350, width=160, height=40)
            buttonOk = tk.Button(root5_1_2_1, font=('Arial', 11), text='下一页',
                                      command=lambda: back(root5_1_2_1,'a3b1','we'))
            buttonOk.place(x=300, y=300, width=60, height=40)
        else:
            t=0
            buttonOk = tk.Button(root5_1_2_1,  font=('Arial', 11), text='已是最后一页,点击返回主页面',
                                          command=lambda: back(root5_1_2_1))
            buttonOk.place(x=180, y=300, width=250, height=40)
        root5_1_2_1.mainloop()


    root5_1_2 = tk.Tk()
    root5_1_2.title('图书信息查询')
    root5_1_2.geometry('700x400')
    select_book_1()
    root5_1_2.mainloop()

now=datetime.datetime.now()
day_year=now.year
day_month=now.month
day_day=now.day
day_now=('{}/{}/{}').format(day_year,day_month,day_day)

window = tk.Tk()
window.title('欢迎使用图书管理系统')
window.geometry('450x300')

# user information
tk.Label(window, text='User name: ').place(x=50, y=150)
tk.Label(window, text='Password: ').place(x=50, y=190)

var_usr_name = tk.StringVar()
entry_usr_name = tk.Entry(window, textvariable=var_usr_name)
entry_usr_name.place(x=160, y=150)
var_usr_pwd = tk.StringVar()
entry_usr_pwd = tk.Entry(window, textvariable=var_usr_pwd, show='*')
entry_usr_pwd.place(x=160, y=190)

def usr_login():
    usr_name = var_usr_name.get()
    usr_pwd = var_usr_pwd.get()
    try:
        with open('usrs_info.pickle', 'rb') as usr_file:
            usrs_info = pickle.load(usr_file)
            print(usrs_info)
    except FileNotFoundError:
        with open('usrs_info.pickle', 'wb') as usr_file:
            usrs_info = {'admin': 'admin'}
            pickle.dump(usrs_info, usr_file)
    if usr_name in usrs_info:
        if usr_pwd == usrs_info[usr_name]:
            window.destroy()
            return menu()
            cursor.close()
        else:
            tk.messagebox.showerror(message='Error, your password is wrong, try again.')
    else:
        is_sign_up = tk.messagebox.askyesno('Error, your a is wrong, try again.')

def usr_sign_up():
    #确认注册函数,以便后面调用
    def signyes():
        username = sign_up_name.get()
        password = sign_up_pwd.get()
        confirmpass = sign_up_conf.get()
        try:
            with open('usrs_info.pickle','rb') as usr_file:
                exist_usr_info = pickle.load(usr_file)
        except FileNotFoundError:
            exist_usr_info = {}
        if username in exist_usr_info:
            tk.messagebox.showerror(message='用户已存在!')
        elif username == '' and password == '':
            tk.messagebox.showerror(message='用户名和密码不能为空!')
        elif password != confirmpass:
            tk.messagebox.showerror(message='密码前后不一致!')
        else:
            exist_usr_info[username] = password
            with open('usrs_info.pickle', 'wb') as usr_file:
                pickle.dump(exist_usr_info, usr_file)
                tk.messagebox.showinfo(message='注册成功!')
                window_sign.destroy()  #存完了就销毁页面

    #新建注册窗口
    window_sign = tk.Toplevel(window)
    window_sign.geometry('450x300')
    window_sign.title('sign up')
    #注册组件的文字部分
    tk.Label(window_sign, text='username: ').place(x=50, y=130)
    tk.Label(window_sign, text='password: ').place(x=50, y=160)
    tk.Label(window_sign, text='confirmpass: ').place(x=50, y=190)
    # 注册组件框部分
    sign_up_name = tk.StringVar()
    sign_up_pwd = tk.StringVar()
    sign_up_conf = tk.StringVar()
    enter_sign_up_name = tk.Entry(window_sign, textvariable=sign_up_name)
    enter_sign_up_name.place(x=160, y=130)
    enter_sign_up_pwd = tk.Entry(window_sign, textvariable=sign_up_pwd, show='*')
    enter_sign_up_pwd.place(x=160, y=160)
    enter_sign_up_conf = tk.Entry(window_sign, textvariable=sign_up_conf, show='*')
    enter_sign_up_conf.place(x=160, y=190)
    #确认注册按钮
    btn_confirm = tk.Button(window_sign, text='确定', command=signyes)
    btn_confirm.place(x=180, y=230)

# login and sign up button
btn_login = tk.Button(window, text='Login', command=usr_login) #这里command是方法
btn_login.place(x=170, y=230)
btn_sign_up = tk.Button(window, text='Sign up', command=usr_sign_up)
btn_sign_up.place(x=270, y=230)

window.mainloop()

这之中登录界面子程序是在网上看到有作者发的就拿来用了,如果不能发请联系我删除。

输出作者信息和图书信息使用到的类计数器是改的朋友的代码,会有一些没有出现在本程序里面的参数导致影响阅读体验,但是本着代码能跑就不改的原则我没有进行更改,希望大家原谅

文章出处登录后可见!

已经登录?立即刷新

共计人评分,平均

到目前为止还没有投票!成为第一位评论此文章。

(0)
乘风的头像乘风管理团队
上一篇 2023年7月12日
下一篇 2023年7月12日

相关推荐

此站出售,如需请站内私信或者邮箱!