期末老师布置的大作业，没地方存，丢这吧
只实现了部分功能
用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")
#
# =============================================================================
4706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
