sqlite数据库具体的结构设计和数据的截图放最后,因为图片有点多,但是能更好的帮助大家理解和实现该系统
目录
一、任务描述
基于python语言,实现图书馆图书管理的一般功能,要有管理员和用户两个角色。用户角色功能包括借书、还书、查询某本图书信息和图书状态、查询自己已借图书信息与状态等。管理员角色的功能包括录入图书、删除图书、修改图书信息、查询某本图书信息和状态、查询任意用户借书状态、总览图书馆图书信息和状态。
所设计的系统以菜单方式工作,为用户提供清晰的使用提示,依据用户的选择来进行各种处理。图书信息包括编号、书名、作者、出版社、出版日期、价格。录入的图书信息使用SQLite数据库保存,图书数量不少于20本。用户数量不少于10个。每个图书都有三个副本可供读者借阅。每个读者一次最多借两本书。
管理系统功能解释(包括但不局限于):
- 录入:即增加一本书的记录到系统中。
- 存储:即将图书信息保存在数据库中。
- 查询:可根据书名、编号查找相关图书信息,若找到显示该书全部信息。用户可以查询已借的所有图书。管理员可以查询任一本书的信息和状态。管理员还可以总览所有书的信息和状态。
- 修改:可修改一本书的除书名外其它信息。
- 图书状态:提供该书在库、不在库的状态,该图书还有几个副本可借,归还期限、已超期时间等。
- 借书:用户借书,有超期图书未还不能借新书。
- 还书:用户还书。
- 能实现多用户的注册和登录,实现管理员登录
二、功能实现
用户功能:
(1)借书
(2)还书
(3)查询某本图书信息和图书状态
(4)查询自己已借图书信息与状态
(5)用户注册和登录
管理功能:
(1)管理员登录
(2)录入图书
(3)删除图书
(4)修改图书信息
(5)查询某本图书信息和状态
(6)查询任意用户借书状态
(7)总览图书馆图书信息和状态。
三、数据库设计
![](https://i-blog.csdnimg.cn/blog_migrate/ec0bbe0505972d35999a52f1cb1c0a85.png)
Book (图书信息表):包括编号(number)、书名(name)、作者(author)、出版社(press)、出版日期(pubdate)、价格(price)
Bookstate(图书状态表):包括副本编号(id)、用户编号(username)、图书编号(number)、是否在库(status),借书日期(borrowtime)、归还日期(returntime)、是否借出标志(flag)。
Login(用户表信息表):账号(username)、密码(password)。
manager(管理员表格):账号(username)、密码(password)。(后面新加的功能,所以图里里面没有)
四、程序流程图(某些新加功能没有)
五、python代码(带注释)
import sqlite3
import datetime
from datetime import datetime
overtime_flag = 2 #超期标志
def getconn(): # 连接数据库
dbstr = "D:\学习\科目学习\专业综合训练2\PCT.db"
conn = sqlite3.connect(dbstr)
cur = conn.cursor()
sqlstr = "create table if not exists Book(number nchar(10) primary key,name nchar(50)," \
"author nchar(20),press nchar(50),pubdate nchar(50),price float)"
cur.execute(sqlstr)
sqlstr = "create table if not exists Bookstate(id int ,number nchar(10),status nchar(10) default '在库'," \
"borrowtime date, returntime date,overtime nchar(20),flag int default 1," \
"foreign key(number) references Book(number))"
cur.execute(sqlstr)
sqlstr = "create table if not exists Login(username primary key unique,password not null)"
cur.execute(sqlstr)
sqlstr = "create table if not exists manager(username nchar(10) primary key unique,password nchar(10) not null)"
cur.execute(sqlstr)
conn.commit()
return conn
# 管理员查询图书状态
def querycopy(number):
conn = getconn()
cur = conn.cursor()
recorde = cur.execute(f"select * from Bookstate where Bookstate.number = {number}").fetchall()
num = 0
for i in recorde: # 计算图书还有多少副本可以借
if i[6] == 1:
num += 1
if num == 0:
print(f"编号为{number}的书,不在库,没有副本可以借")
else:
print(f"编号为{number}的书,在库,还有{num}个副本可以借")
j = 3
today = datetime.today()
while num < j: #控制输出次数,还有几个副本可以借
for i in recorde:
if i[4] != None: #归还时间不为空才继续执行,不然datetime.strptime会报错
if today > datetime.strptime(i[4], '%Y-%m-%d') and i[6] == 0: #将字符串格式化为日期,避免输入错误
print(f"副本{j - num},借书时间:{i[3]},归还期限{i[4]},已超期时间:{today - datetime.strptime(i[4],'%Y-%m-%d')}")
else:
print(f"副本{j - num},借书时间:{i[3]},归还期限{i[4]},未超期")
j -= 1
cur.close()
# 显示所有书籍
def showall():
conn = getconn()
cur = conn.cursor()
recorde = cur.execute("select * from Book").fetchall()
for re in recorde:
print(re)
querycopy(re[0])
print()
##recorde=cur.execute("select * from Bookstate where Bookstate.number = Book.number").fetchall()
cur.close()
def getdata():
number = input("请输入图书编号:")
name = input("请输入图书名字:")
author = input("请输入作者:")
press = input("请输入出版社:")
pubdate = input("请输入出版时间:")
price = eval(input("请输入价格:"))
return number, name, author, press, pubdate, price
# 添加书籍
def addbook():
conn = getconn()
cur = conn.cursor()
records = getdata()
sqlstr = "insert into Book (number,name,author,press,pubdate,price)" \
"values(?,?,?,?,?,?)"
# 每本书3个副本
cur.execute(sqlstr, (records[0], records[1], records[2], records[3], records[4], records[5]))
cur.execute(f"insert into Bookstate(id,number) values(1,{records[0]})")
cur.execute(f"insert into Bookstate(id,number) values(2,{records[0]})")
cur.execute(f"insert into Bookstate(id,number) values(3,{records[0]})")
print("插入成功!")
conn.commit()
print(records[0], records[1], records[2], records[3], records[4], records[5])
cur.close()
# 查询图书信息
def querybook():
conn = getconn()
cur = conn.cursor()
op = input("按照编号查询,还是书名查询,请输入:")
num = None
name = None
if op == "编号":
num = input("请输入编号:")
cur.execute("select * from Book where number=:num", {"num": num})
record = cur.fetchall()
for line in record:
print(line)
elif op == "书名":
name = input("请输入书名:")
cur.execute("select * from Book where name=:name", {"name": name})
record = cur.fetchall()
for line in record:
print(line)
cur.close()
return [num, name]
# 删除图书
def delbook():
conn = getconn()
cur = conn.cursor()
op = input("删除图书信息,你要输入编号还是书名:")
if op == '编号':
num = input("请输入编号:")
cur.execute("select * from Book where number=:num", {"num": num})
record = cur.fetchall()
for line in record:
print(line)
cur.execute("delete from Book where number=:num", {"num": num})
cur.execute("delete from Bookstate where number=:num", {"num": num})
elif op == '书名':
name = input("请输入书名:")
cur.execute("select * from Book where name=:name", {"name": name})
record = cur.fetchall()
for line in record:
print(line)
cur.execute("delete from Bookstate where number in(select number from Book where name=:name)", {"name": name})
cur.execute("delete from Book where name=:name", {"name": name})
print("该图书删除成功!")
conn.commit()
cur.close()
# 修改图书信息
def udbook():
conn = getconn()
cur = conn.cursor()
num = input("请输入图书编号:")
row = cur.execute("select * from Book where number=?", (num,)).fetchall()
if not row:
print("没有该图书,请重试!")
return 0
print(row)
print("请输入修改后的信息(图书名字不能修改):")
record = getdata()
sqlstr = "update Book set number=?,author=?,press=?,pubdate=?,price=? where number=?"
cur.execute(sqlstr, (record[0], record[2], record[3], record[4], record[5], num))
sqlstr = "update Bookstate set number=? where number=?"
cur.execute(sqlstr, (record[0], num))
print("修改成功")
conn.commit()
cur.close()
# 判断操作是否继续执行
def continueif():
op = input("是否继续操作:y/n? 请输入:")
if str.lower(op) == 'y':
return 1
elif str.lower(op) == 'n':
return 0
else:
print("输入错误")
def queryuser(): #管理员界面查询用户的借书状态
username = input("请输入所要查询用户的编号:")
querymybook(username)
def managerlog():
conn = getconn()
cur = conn.cursor()
username = input("请输入管理员账号:")
password = input("请输入账号密码:")
row = cur.execute("select * from manager where username=? and password=?", (username, password)).fetchone()
if row is not None:
print("登录成功!")
return 1
else:
print("账号或者密码错误,登陆失败!")
return 0
def manager():
flag = 1
while flag == 1:
line = "--------------------管理员界面------------------------------------"
print(line)
print("退出管理员界面:0 录入图书信息:1 删除图书信息:2 修改图书信息:3 ")
print("查询某本图书信息和状态:4 查询任意用户借书状态:5 总览图书信息和状态:6")
status = int(input("请输入选项:"))
match status:
case 0:
break
case 1:
addbook()
case 2:
delbook()
case 3:
udbook()
case 4:
queryms()
case 5:
queryuser()
case 6:
showall()
case _:
print("没有该选项")
flag = continueif()
def log():
conn = getconn()
cur = conn.cursor()
username = input("请输入登录账号:")
password = input("请输入登录密码:")
row = cur.execute("select * from Login where username=? and password=?", (username,password)).fetchone()
if row is not None:
print("登录成功!")
return [1, username]
else:
print("账号或者密码错误,登陆失败!")
return [0, None]
def register():
conn = getconn()
cur = conn.cursor()
username = input("请输入注册账号:")
password = input("请输入注册密码:")
cur.execute("insert into Login(username,password) values(?,?)", (username, password))
print(f"注册成功!账号:{username},密码:{password} ")
conn.commit()
cur.close()
def overtime(username): #判断是否有超期图书未归还
conn = getconn()
cur = conn.cursor()
recorde = cur.execute("select * from Bookstate where username=?", (username,)).fetchall()
if not recorde:
return 0 #如果一条记录都没有,说明还未借书,也就没有超期图书
today = datetime.today()
for i in recorde:
if i[4] != None: # 归还时间不为空才继续执行,不然datetime.strptime会报错
if today > datetime.strptime(i[4], '%Y-%m-%d') and i[6] == 0: # 将字符串格式化为日期,避免输入错误
recorde1 = cur.execute("select number,name from Book where number in "
"(select number from Bookstate where username=? and number=?)", (i[7],i[1])).fetchall()
print(f"图书编号:{recorde1[0][0]},图书名字:{recorde1[0][1]},副本{i[0]},借书时间:{i[3]},归还期限:{i[4]},"
f"已超期时间:{today - datetime.strptime(i[4], '%Y-%m-%d')}")
global overtime_flag
overtime_flag = 0
cur.close()
def borrowbook(username): #user登录时的账号,在借书时使用此账号
conn = getconn()
cur = conn.cursor()
#username = input("请输入账号进行借书:")
op = overtime(username)
if overtime_flag == 0:
print(f"你有超期图书未归还,请归还后才能借阅")
return 0 #有超期图书不能借书
row = cur.execute("select count(*) as count1 from Bookstate where username=?", (username,)).fetchone()
count = row[0]
if count >= 2:
print("对不起,一个账号一次只能借阅两本书,你已经达到数量上限")
return 0
op = input("输入图书编号或者名字,请选择:")
if op == '编号':
number = input("请输入图书编号:")
recorde = cur.execute("select * from Book where number=?", (number,)).fetchall()
print(f"图书信息:{recorde}")
recorde = cur.execute("select * from Bookstate where number = ?", (number,)).fetchall()
num = 0
for i in recorde: # 计算图书还有多少副本可以借
if i[6] == 1:
num += 1
if num == 0:
print("该书已经没有副本可以借阅,请选择其他书籍")
else:
borrowtime = input("请输入借阅时间(yyyy-mm-dd):")
returntime = input("请输入归还时间(yyyy-mm-dd):")
if num == 3: #还有3个副本,从第一个开始借阅
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
"where id=1 and number=?"
elif num == 2:
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
"where id=2 and number=?"
elif num == 1:
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
"where id=3 and number=?"
cur.execute(sqlstr, ("不在库", borrowtime, returntime, username, number))
elif op == "名字":
name = input("请输入图书名字:")
recorde = cur.execute("select * from Book where name=?", (name,)).fetchall()
print(f"图书信息:{recorde}")
recorde = cur.execute("select * from Bookstate where number in(select number from Book where name=?)",
(name,)).fetchall()
number = recorde[0][1]
num = 0
for i in recorde: # 计算图书还有多少副本可以借
if i[6] == 1:
num += 1
if num == 0:
print("该书已经没有副本可以借阅,请选择其他书籍")
else:
borrowtime = input("请输入借阅时间(yyyy-mm-dd):")
returntime = input("请输入归还时间(yyyy-mm-dd):")
if num == 3: # 还有3个副本,从第一个开始借阅
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
"where id=1 and number=?"
elif num == 2:
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
"where id=2 and number=?"
elif num == 1:
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
"where id=3 and number=?"
cur.execute(sqlstr, ("不在库", borrowtime, returntime, username, number))
conn.commit()
cur.close()
def querymybook(username): #用户查询已借图书与状态
conn = getconn()
cur = conn.cursor()
recorde = cur.execute("select * from Bookstate where username=?", (username,)).fetchall()
#recorde1 = cur.execute("select * from Book where number in "
# "(select number from Bookstate where username=?)", (recorde[0][7],)).fetchall()
today = datetime.today()
for i in recorde:
if i[4] != None: # 归还时间不为空才继续执行,不然datetime.strptime会报错,且归还时间为空说明此书未借
if today > datetime.strptime(i[4], '%Y-%m-%d') and i[6] == 0: # 将字符串格式化为日期,避免输入错误
recorde1 = cur.execute("select * from Book where number in "
"(select number from Bookstate where username=? and number=?)", (i[7],i[1])).fetchall()
print(f"图书信息:图书编号:{recorde1[0][0]},图书名字:{recorde1[0][1]},作者:{recorde1[0][2]},"
f"出版社:{recorde1[0][3]},出版时间:{recorde1[0][4]},价格:{recorde1[0][5]}")
print(f",副本{i[0]},借书时间:{i[3]},归还期限:{i[4]},已超期时间:{today - datetime.strptime(i[4], '%Y-%m-%d')}\n")
else:
recorde1 = cur.execute("select * from Book where number in "
"(select number from Bookstate where username=? and number=?)", (i[7],i[1])).fetchall()
print(f"图书信息:图书编号:{recorde1[0][0]},图书名字:{recorde1[0][1]},作者:{recorde1[0][2]},"
f"出版社:{recorde1[0][3]},出版时间:{recorde1[0][4]},价格:{recorde1[0][5]}")
print(f",副本{i[0]},借书时间:{i[3]},归还期限:{i[4]},未超期\n")
if not recorde:
print("信息为空,还未借书")
return 0
def returnbook(username): #还书
conn = getconn()
cur = conn.cursor()
print("你所借图书信息与状态")
line = "-------------------------------------------"
print(line)
op = querymybook(username)
print(line)
if op == 0:
return 0 #还没借书,直接退出
number = input("请输入所还书籍的编号:")
sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=?,username=? " \
"where number=? and username=?"
cur.execute(sqlstr, ('在库', None, None, 1, None, number, username))
print("还书成功")
conn.commit()
cur.close()
#recorde = cur.execute("select * from Bookstate where username=?", (username,)).fetchall()
#recorde1 = cur.execute("select number,name from Book where number in "
# "(select number from Bookstate where username=?)", (username,)).fetchall()
def queryms(): #查询图书信息和状态
conn = getconn()
cur = conn.cursor()
op = querybook() #查询图书信息
if op[0] != None:
querycopy(op[0]) #查询图书状态
elif op[1] != None:
num = cur.execute("select number from Book where name =?", (op[1],)).fetchall()
querycopy(num[0][0]) #查询图书状态
def user():
flag = 1
op = 2
ch=2
while ch == 2:
ch = int(input("登录账户:1 注册账户:2 请输入选项:"))
global overtime_flag
overtime_flag = 2 #切换账号时需要把超期标志赋值,不然会影响后面的账号
if ch == 1:
op = log()
elif ch == 2:
register()
if op[0] == 1:
while flag == 1:
line = "--------------------用户界面-----------------------"
print(line)
print("退出用户界面:0 借书:1 还书:2 ")
print("查询图书信息和状态:3 查询自己已借图书信息和状态:4 ")
status = int(input("请输入选项:"))
match status:
case 0:
break
case 1:
borrowbook(op[1])
case 2:
returnbook(op[1])
case 3:
queryms()
case 4:
querymybook(op[1])
case _:
print("没有该选项")
flag = continueif()
else:
print("欢迎下次光临!")
if __name__ == "__main__":
a = 1
line = "--------------------主界面-----------------------"
while a == 1:
print(line)
op = int(input("退出系统:0 管理员使用:1 用户使用:2 :"))
if op == 1:
ch = managerlog()
if ch == 1:
manager()
elif op == 2:
user()
elif op == 0:
break
else:
print("输入错误请重试!\n")
六、具体数据库结构设计截图
1.Book表