期末老师布置的大作业,没地方存,丢这吧
只实现了部分功能
用navicat创数据库
数据库里有三个表,books(bno,bname,author),borrow(bno,rno,appointment,prestamo_time,fee),reader(rno,password,rname,estado)
还有个名为fee的视图,定义为
select `borrow`.`rno` AS `rno`,`borrow`.`fee` AS `fee` from `borrow`
# -*- coding: utf-8 -*-
"""
Created on Fri Dec 16 22:07:10 2022
@author: errormaker
"""
import datetime
import pymysql
def connecting(userr:str='publicuser',passwordd='123456'): #配合Navicat for mysql使用
conn=pymysql.connect(
host='localhost',
user=userr,
password=passwordd,
port=3306,
charset='utf8',
db='estar'
)
if conn:
print("连接成功")
return conn
administrator=['root','root1','root2']#三个管理员
def lookall(table:str):
sql=conn.cursor()
sql.execute("select * from %s" %(table))
conn.commit()
result=sql.fetchall()
print("当前表内数据为:",end='\n')
for i in result:
print(i)
def addoperate(): #管理员对所有表添加数据------------------------------------------
while True:
print("\n你想在哪个表增加数据?1:books;2:reader;3:borrow;0:退出")
a=(input("请输入1或2或3或0:"))
if a=='1':
sql=conn.cursor()
print("按照“图书编号”“图书名称”“作者”顺序来输入数据:")
bno=input("图书编号:")
bname=input("图书名称:")
author=input("作者:")
# print("INSERT INTO %s VALUES(%s,%s,%s)" %('books',repr(bno),repr(bname),repr(author)) )
sql.execute("INSERT INTO %s VALUES(%s,%s,%s)" %('books',repr(bno),repr(bname),repr(author)) )
conn.commit()
sql=conn.cursor()
sql.execute("select * from %s" %('books'))
conn.commit()
result=sql.fetchall()
print("插入数据后的表为:",end='\n')
for i in result:
print(i)
elif a=='2':
sql=conn.cursor()
print("按照“读者编号”“读者密码”“读者名称”“estado”顺序来输入数据:")
rno=input("读者编号:")
password=input("读者密码:")
rname=input("读者名称:")
estado=input("estado:")
# print("INSERT INTO %s VALUES(%s,%s,%s)" %('books',repr(bno),repr(bname),repr(author)) )
sql.execute("INSERT INTO %s VALUES(%s,%s,%s,%s)" %('reader',repr(rno),repr(password),repr(rname),repr(estado)) )
conn.commit()
sql=conn.cursor()
sql.execute("select * from %s" %('reader'))
conn.commit()
result=sql.fetchall()
print("插入数据后的表为:",end='\n')
for i in result:
print(i)
elif a=='3':
sql=conn.cursor()
print("按照“图书编号”“读者编号”“读者名称”“appointment”“prestamo_time”“fee”顺序来输入数据:")
bno=input("图书编号:")
rno=input("读者编号:")
appointment=input("appointment:")
prestamo_time=input("prestamo_time:")
fee=input("fee:")
sql.execute("INSERT INTO %s VALUES(%s,%s,%s,%s,%s)" %('borrow',repr(bno),repr(rno),repr(appointment),repr(prestamo_time),repr(fee)) )
conn.commit()
sql=conn.cursor()
sql.execute("select * from %s" %('borrow'))
conn.commit()
result=sql.fetchall()
print("插入数据后的表为:",end='\n')
for i in result:
print(i)
elif a=='0':
print("退出成功!")
break
else:
print("请输入正确数字:")
continue
#------------------------------------------------------------------------------
def deleteoperate():#管理员对所有表删除数据
while True:
print("\n你想在哪个表删除数据?1:books;2:reader;3:borrow;0:退出")
a=(input("请输入1或2或3或0:"))
if a=='0':
print("退出成功!")
break
elif a=='1':
lookall('books')
bno=input("输入你要删除数据的书的编号:")
sql=conn.cursor()
sql.execute("DELETE FROM %s where bno=%s" %('books',repr(bno)))
lookall('books')
elif a=='2':
lookall('reader')
rno=input("输入你要删除数据的读者的编号:")
sql=conn.cursor()
sql.execute("DELETE FROM %s where rno=%s" %('reader',repr(rno)))
lookall('reader')
elif a=='3':
lookall('borrow')
bno=input("输入你要删除数据的书的编号:")
rno=input("输入你要删除数据的读者的编号:")
sql=conn.cursor()
sql.execute("DELETE FROM %s where rno=%s and bno=%s" %('borrow',repr(rno),repr(bno)))
lookall('borrow')
else:
print("请输入正确数字:")
continue
#------------------------------------------------------------------------------
def updataoperate():#管理员对所有表修改数据
while True:
print("\n你想在哪个表修改数据?1:books;2:reader;3:borrow;0:退出")
a=(input("请输入1或2或3或0:"))
if a=='0':
print("退出成功!")
break
elif a=='1':
lookall('books')
while True:
b=(input("你想修改哪个属性?1:图书编号;2:图书名称;3:作者(输入数字);0:返回;(输入数字):"))
if b=='1':
before=input("输入原来的图书的编号:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set bno=%s where bno=%s" %('books',repr(after),repr(before)))
conn.commit()
lookall('books')
elif b=='2':
bno=input("输入你想修改的那一行的图书编号:")
before=input("输入原来的图书的名称:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set bname=%s where bname=%s and bno=%s" %('books',repr(after),repr(before),repr(bno)))
conn.commit()
lookall('books')
elif b=='3':
bno=input("输入你想修改的那一行的图书编号:")
before=input("输入你想修改的图书的作者:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set author=%s where author=%s and bno=%s" %('books',repr(after),repr(before),repr(bno)))
conn.commit()
lookall('books')
elif b=='0':
print("返回")
break
else:
print("请输入正确数字:")
continue
elif a=='2':
lookall('reader')
while True:
b=(input("你想修改哪个属性?1:读者编号;2:密码;3:读者姓名;4:estado;0:返回;(输入数字):"))
if b=='1':
before=input("输入原来的读者编号:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set rno=%s where rno=%s" %('reader',repr(after),repr(before)))
conn.commit()
lookall('reader')
elif b=='2':
rno=input("输入你想修改的那一行的读者编号:")
before=input("输入原来的的密码:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set password=%s where password=%s and rno=%s" %('reader',repr(after),repr(before),repr(rno)))
conn.commit()
lookall('reader')
elif b=='3':
rno=input("输入你想修改的那一行的读者编号:")
before=input("输入原来的读者姓名:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set rname=%s where rname=%s and rno=%s" %('reader',repr(after),repr(before),repr(rno)))
conn.commit()
lookall('reader')
elif b=='4':
rno=input("输入你想修改的那一行的读者编号:")
before=input("输入原来的estado:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set estado=%s where estado=%s and rno=%s" %('reader',repr(after),repr(before),repr(rno)))
conn.commit()
lookall('reader')
elif b=='0':
print("返回")
break
else:
print("请输入正确数字:")
continue
elif a=='3':
lookall('borrow')
while True:
b=(input("你想修改哪个属性?1:图书编号;2:读者编号;3:appointment;4:prestamo_time;5:fee;0:返回;(输入数字):"))
if b=='1':
before=input("输入原来的图书编号:")
rno=input("输入你想修改的那一行的读者编号:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set bno=%s where bno=%s and rno=%s" %('borrow',repr(after),repr(before),repr(rno)))
conn.commit()
lookall('borrow')
elif b=='2':
before=input("输入原来的读者编号:")
bno=input("输入你想修改的那一行的图书编号:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set rno=%s where rno=%s and bno=%s" %('borrow',repr(after),repr(before),repr(bno)))
conn.commit()
lookall('borrow')
elif b=='3':
bno=input("输入你想修改的那一行的图书编号:")
rno=input("输入你想修改的那一行的读者编号:")
before=input("输入原来的appointment:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set appointment=%s where appointment=%s and rno=%s and bno=%s" %('borrow',repr(after),repr(before),repr(rno),repr(bno)))
conn.commit()
lookall('borrow')
elif b=='4':
bno=input("输入你想修改的那一行的图书编号:")
rno=input("输入你想修改的那一行的读者编号:")
before=input("输入原来的prestamo_time:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set prestamo_time=%s where prestamo_time=%s and rno=%s and bno=%s" %('borrow',repr(after),repr(before),repr(rno),repr(bno)))
conn.commit()
lookall('borrow')
elif b=='5':
bno=input("输入你想修改的那一行的图书编号:")
rno=input("输入你想修改的那一行的读者编号:")
before=input("输入原来的fee:")
after=input("你想修改为:")
sql=conn.cursor()
sql.execute("update %s set fee=%s where fee=%s and rno=%s and bno=%s" %('borrow',repr(after),repr(before),repr(rno),repr(bno)))
conn.commit()
lookall('borrow')
elif b=='0':
print("返回")
break
else:
print("请输入正确数字:")
continue
#------------------------------------------------------------------------------
def selectoperate():
while True:
print("\n你想查哪个表的数据?1:books;2:reader;3:borrow;0:退出")
a=(input("请输入1或2或3或0:"))
if a=='1':
lookall('books')
elif a=='2':
lookall('reader')
elif a=='3':
lookall('borrow')
elif a=='0':
print("退出成功!")
break
else:
print("请输入正确数字:")
continue
#------------------------------------------------------------------------------
def adminstration():
while True:
print("你想要做什么?\n 1:增加数据;\n 2:删除数据;\n 3:修改数据;\n 4:查询数据;0:退出管理员系统:")
a=(input("请输入1或2或3或4或0:"))
if a=='1':
addoperate()
elif a=='2':
deleteoperate()
elif a=='3':
updataoperate()
elif a=='4':
selectoperate()
elif a=='0':
print("退出管理员系统成功!")
break
else:
print("请输入正确数字:")
continue
#------------------------------------------------------------------------------
def addreader(rno,password,rname,estado=0): #publicuser添加读者------------------------------------------
a='2'
if a=='2':
sql=conn.cursor()
# print("INSERT INTO %s VALUES(%s,%s,%s)" %('books',repr(bno),repr(bname),repr(author)) )
sql.execute("INSERT INTO %s VALUES(%s,%s,%s,%s)" %('reader',repr(rno),repr(password),repr(rname),repr(estado)) )
conn.commit()
sql=conn.cursor()
sql.execute("select * from %s where rno=%s" %('reader',repr(rno)))
conn.commit()
result=sql.fetchall()
print("注册成功;你的信息为:",end='\n')
for i in result:
print(i)
#selectoperate()
#addoperate()
#deleteoperate()
#updataoperate()
conn=connecting() #------连接public用户
print("欢迎进入网上图书馆系统请按要求操作:")
print("提示:你未登录,只能查书")
userr='publicuser'#userr记录当前用户是谁,辨别用户
while True:
print("请按要求操作:")
print('您好%s' %(userr))
a=input("1:查书(借阅)(预约);\n2:登录(切换账号);\n3:注册;\n4:修改密码;\n5:查询借阅记录;\n6:管理(数据库管理员专用);\n0:退出系统:\n 请输入1或2或3或4或5或6或0:")
if a=='1':
cursor=conn.cursor()
while True:
b=(input("请输入您要查询的书籍:(支持单字或全称)\n输入0退出查询:"))
cursor.execute('select * from books where bname like %s;' %(repr("%"+b+"%")))
result=cursor.fetchall()
if result:
print('查询到的结果:\n')
for i in result:
print(i)
while True:
whatwant=input("1:借阅;\n3:返回:请输入1或3:")
if whatwant=='1':
while True:
if_borrow=input("你是否真想借阅这本书?\n 1:借阅;2:不借阅:\n 请输入1或2:")
if if_borrow=='1':
if userr!='publicuser':
cursor.execute('select bno from books where bname like %s;' %(repr("%"+b+"%")))
result1=cursor.fetchall()#result1是查询到的图书结果bno
result1=result1[0][0]
cursor.execute('SELECT prestamo_time FROM borrow WHERE bno=%s ' %(repr(result1)))
result2=cursor.fetchall()#result2是看这本书有没有被借阅
#print(result2)
if result2==() or result2=='0':#如果结果是空的,也代表没借
result2=()#写这个if的目的是防止返回空值,result2[0]超限报错##-----
####时间#
#时间
#时间
else:
result2=result2[0][0]
#得到prestamo-time
#print(result2)
if result1 and (result2==()):#result==‘0’#代表该书能被查到,并且没有被借阅
addhours=datetime.datetime.now()+datetime.timedelta(days=14)
time=addhours.strftime('%Y-%m-%d')
cursor.execute('insert into borrow VALUES (%s,%s,"0",%s,"0")' %(repr(result1),repr(userr),repr(time)))
conn.commit()
#注意当前用户有没有对borrow的插入权限,不然插入操作无效
print("您已经借阅成功,您只能借阅14天,超时未还将无法登录")
cursor.close()
break
elif result1 and result2:#一个账号只能借阅一本书
print("您无法借阅因为正在被别人借阅,系统已经自动帮您预约")
cursor.execute('insert into borrow VALUES (%s,%s,"1","0","0")' %(repr(result1),repr(userr)))
conn.commit()
cursor.close()
break
else:
print("\n您还未登陆,请先登录")
break
else:
print("请输入正确数字!")
continue
elif whatwant=='3':
print("返回")
break
else:
print("请输入正确数字!")
continue
break
break
elif b=='0':
print('\n退出查询成功:')
break
else:
print("未找到此书籍,请重新输入或输入0退出查询:")
continue
cursor.close()
elif a=='2':#登录
userrr=input("请输入用户名(账号):")
if userrr not in administrator:
dl=conn.cursor()
dl.execute("select rno from reader")
r=dl.fetchall()
namee=[]
for j in range(0,len(r)):
namee+=r[j]
if userrr in namee:
number=0
while True:
passwordd=input("请输入密码:")
dl=conn.cursor()
dl.execute("select * from reader where rno=%s and password=%s" %(repr(userrr),repr(passwordd)))
if (dl.fetchall()):
conn=(connecting(userrr,passwordd))
cursor=conn.cursor()
cursor.execute('SELECT fee FROM fee WHERE rno=%s' %(repr(userrr)) )#查询是否欠费,若欠费无法借阅书籍
fee=cursor.fetchall()
if fee!=():
fe=fee[0][0]
if fe=='1':
print('查询到您有一本书过期未还,你可以选择续借或归还图书后联系管理员恢复')
while True:
xujie=input("是否续借?\n1:是;\n2:否:\n输入数字:")
if xujie=='2':
break
elif xujie=='1':
addhours=datetime.datetime.now()+datetime.timedelta(days=14)
time=addhours.strftime('%Y-%m-%d')
curso=conn.cursor()
curso.execute('update borrow set prestamo_time=%s where rno=%s ' %(repr(time),repr(userrr)))
conn.commit()
curso.execute('update borrow set fee="0" where rno=%s ' %(repr(userrr)))
conn.commit()
print("续借成功")
curso.close()
break
break
else:
print("用户可借阅")
else:
print("用户可借阅")
userr=userrr
print("欢迎%s" %(userr))
break
else:
print("密码错误,已错误次数:")
print(number)
number+=1
if number<=3:
continue
else:
print("累积错误三次,请重试!")
break
else:
print("无该用户,请检查用户名或返回注册!")
elif userrr in administrator:
number=0
while True:
passwordd=input("请输入密码:")
if passwordd=='123456':
conn=(connecting(userrr,passwordd))
userr=userrr
print("欢迎%s" %(userr))
break
else:
print("密码错误,已错误次数:")
print(number)
number+=1
if number<=3:
continue
else:
print("累积错误三次,若遗忘密码可联系管理员重置!")
break
# dl=conn.cursor()
# dl.execute("select * from reader where rno=%s and password=%s" %(repr(userrr),repr(passwordd)))
# if (dl.fetchall()):
# conn=(connecting(userrr,passwordd))
# userr=userrr
# print("欢迎%s" %(userr))
# break
# else:
# print("密码错误,已错误次数:")
# print(number)
# number+=1
# if number<=3:
# continue
# else:
# print("累积错误三次,请重试!")
# print(name)
elif a=='3':
print("注册系统:")
if userr == 'publicuser' or userr == 'root':
while True:
userr_temp=input("请输入用户名(账号);输入0退出注册:")
zhuce=conn.cursor()
zhuce.execute("select rno from reader")
r=zhuce.fetchall()
name=[]
for j in range(0,len(r)):
name+=r[j]
# print(name)
if userr_temp=='0':
print('\n退出成功:')
break
elif userr_temp in name:
print("提示:已经有用户占用了此名字")
continue
else:
userr=userr_temp
while True:
passwordd1=input("请输入密码:")
passwordd2=input("请再次输入密码:")
if passwordd1==passwordd2:
passwordd=passwordd2
break
else:
print("两次密码不一致!")
continue
#注册
zhuce=conn.cursor()
#赋权
zhuce.execute("CREATE USER %s@'localhost' IDENTIFIED BY %s" %(repr(userr),repr(passwordd)))
zhuce.execute("GRANT INSERT, UPDATE,SELECT ON `estar`.`borrow` TO %s@'localhost'" %(repr(userr)))
zhuce.execute("GRANT SELECT ON `estar`.`books` TO %s@'localhost'" %(repr(userr)))
zhuce.execute("GRANT SELECT ON `estar`.`reader` TO %s@'localhost'" %(repr(userr)))
zhuce.execute("GRANT SELECT ON `estar`.`fee` TO %s@'localhost'" %(repr(userr)))
zhuce.execute("GRANT UPDATE ON `estar`.`reader` TO %s@'localhost'" %(repr(userr)))
rname=input("输入读者昵称:")
addreader(userr,passwordd,rname)
break
else:
print("请退出当前用户后再注册")
elif a=='4':#改密码
if userr=='publicuser':
print("\n请先登录账号")
else:
while True:
pre_password=input("请输入之前的密码,输入000放弃修改:")
if pre_password!='000':
xgmm=conn.cursor()
xgmm.execute("select * from reader where rno=%s and password=%s" %(repr(userr),repr(pre_password)))
if (xgmm.fetchall()):
while True:
passwordd1=input("请输入修改后的密码:")
passwordd2=input("请再次输入修改后的密码:")
if passwordd1==passwordd2:
later_passwordd=passwordd2
break
else:
print("两次密码不一致!")
continue
#在表里修改密码
before=pre_password
after=later_passwordd
sql=conn.cursor()
sql.execute("update %s set password=%s where password=%s and rno=%s" %('reader',repr(after),repr(before),repr(userr)))
#在服务器修改密码
mm=conn.cursor()
mm.execute("set password for %s@localhost = password(%s)" %(repr(userr),repr(later_passwordd)))
conn.commit()
print("成功修改%s的密码" %(userr))
break
#else语句:密码不匹配则提示改不了,输入0放弃修改
else:
print("密码错误!")
else:
print("返回成功!")
break
elif a=='5':
the_user=userr
if the_user=='publicuser':
print("请登录账号")
else:
print("你的借阅记录为:")
find=conn.cursor()
find.execute("SELECT bname FROM books WHERE bno in (SELECT bno FROM borrow WHERE rno=%s)" %(repr(the_user)))
out=find.fetchall()
print(out)
elif a=='6':
if userr in administrator:
print('\n管理员%s,欢迎进入管理系统:' %(userr))
adminstration()
else:
print('你不是管理员,当前用户是%s' %(userr))
continue
elif a=='0':
print("退出成功!")
break
else:
print("请输入正确数字:")
continue
#lookall('books')
# =============================================================================
#
# def findBno(selectwhat:list,fromwhat:str): #查找(无where语句)
# if not(selectwhat):#没有就默认查所有
# selectwhat='*'
# if not(fromwhat):#没有就默认为图书表
# fromwhat='books'
# cursor = conn.cursor()
# cursor.execute("select %s from %s" %(selectwhat,','.join(str(i) for i in fromwhat)))
# result=cursor.fetchall()
# if result:
# print(result)
# else:
# print("\n not found")
# a=''
# b=['books','reader','borrow']
# c=''
# findBno(a,b)
#
# def findBno1(selectwhat:str,fromwhat:str,wherewhat:str()): #查找(有where语句)
# if not(selectwhat):
# selectwhat='*'
# if not(fromwhat):
# fromwhat='books'
# cursor = conn.cursor()
# cursor.execute("select %s from %s where %s" %(selectwhat,fromwhat,wherewhat))
# result=cursor.fetchall()
# if result:
# print(result)
# else:
# print("\n not found")
#
# =============================================================================