当前位置:   article > 正文

图书信息管理系统(python,sqlite)_python图书馆管理系统

python图书馆管理系统

       sqlite数据库具体的结构设计和数据的截图放最后,因为图片有点多,但是能更好的帮助大家理解和实现该系统 

目录

一、任务描述

二、功能实现

三、数据库设计​编辑

四、程序流程图(某些新加功能没有)

 五、python代码(带注释)

六、具体数据库结构设计截图

1.Book表

 2.Bookstate表​编辑​编辑

 3.Login表​编辑​编辑

 4.manager表​编辑​编辑


一、任务描述

       基于python语言,实现图书馆图书管理的一般功能,要有管理员和用户两个角色。用户角色功能包括借书、还书、查询某本图书信息和图书状态、查询自己已借图书信息与状态等。管理员角色的功能包括录入图书、删除图书、修改图书信息、查询某本图书信息和状态、查询任意用户借书状态、总览图书馆图书信息和状态。

       所设计的系统以菜单方式工作,为用户提供清晰的使用提示,依据用户的选择来进行各种处理。图书信息包括编号、书名、作者、出版社、出版日期、价格。录入的图书信息使用SQLite数据库保存,图书数量不少于20本。用户数量不少于10个。每个图书都有三个副本可供读者借阅。每个读者一次最多借两本书。
管理系统功能解释(包括但不局限于):

  1. 录入:即增加一本书的记录到系统中。
  2. 存储:即将图书信息保存在数据库中。
  3. 查询:可根据书名、编号查找相关图书信息,若找到显示该书全部信息。用户可以查询已借的所有图书。管理员可以查询任一本书的信息和状态。管理员还可以总览所有书的信息和状态。
  4. 修改:可修改一本书的除书名外其它信息。
  5. 图书状态:提供该书在库、不在库的状态,该图书还有几个副本可借,归还期限、已超期时间等。
  6. 借书:用户借书,有超期图书未还不能借新书。
  7.  还书:用户还书。
  8. 能实现多用户的注册和登录,实现管理员登录

二、功能实现

用户功能:
(1)借书
(2)还书
(3)查询某本图书信息和图书状态
(4)查询自己已借图书信息与状态
(5)用户注册和登录

管理功能:
(1)管理员登录
(2)录入图书
(3)删除图书
(4)修改图书信息
(5)查询某本图书信息和状态
(6)查询任意用户借书状态
(7)总览图书馆图书信息和状态。

三、数据库设计

Book (图书信息表):包括编号(number)、书名(name)、作者(author)、出版社(press)、出版日期(pubdate)、价格(price)

Bookstate(图书状态表):包括副本编号(id)、用户编号(username)、图书编号(number)、是否在库(status),借书日期(borrowtime)、归还日期(returntime)、是否借出标志(flag)。

Login(用户表信息表):账号(username)、密码(password)。

manager(管理员表格):账号(username)、密码(password)。(后面新加的功能,所以图里里面没有)

四、程序流程图(某些新加功能没有)

 五、python代码(带注释)

  1. import sqlite3
  2. import datetime
  3. from datetime import datetime
  4. overtime_flag = 2 #超期标志
  5. def getconn(): # 连接数据库
  6. dbstr = "D:\学习\科目学习\专业综合训练2\PCT.db"
  7. conn = sqlite3.connect(dbstr)
  8. cur = conn.cursor()
  9. sqlstr = "create table if not exists Book(number nchar(10) primary key,name nchar(50)," \
  10. "author nchar(20),press nchar(50),pubdate nchar(50),price float)"
  11. cur.execute(sqlstr)
  12. sqlstr = "create table if not exists Bookstate(id int ,number nchar(10),status nchar(10) default '在库'," \
  13. "borrowtime date, returntime date,overtime nchar(20),flag int default 1," \
  14. "foreign key(number) references Book(number))"
  15. cur.execute(sqlstr)
  16. sqlstr = "create table if not exists Login(username primary key unique,password not null)"
  17. cur.execute(sqlstr)
  18. sqlstr = "create table if not exists manager(username nchar(10) primary key unique,password nchar(10) not null)"
  19. cur.execute(sqlstr)
  20. conn.commit()
  21. return conn
  22. # 管理员查询图书状态
  23. def querycopy(number):
  24. conn = getconn()
  25. cur = conn.cursor()
  26. recorde = cur.execute(f"select * from Bookstate where Bookstate.number = {number}").fetchall()
  27. num = 0
  28. for i in recorde: # 计算图书还有多少副本可以借
  29. if i[6] == 1:
  30. num += 1
  31. if num == 0:
  32. print(f"编号为{number}的书,不在库,没有副本可以借")
  33. else:
  34. print(f"编号为{number}的书,在库,还有{num}个副本可以借")
  35. j = 3
  36. today = datetime.today()
  37. while num < j: #控制输出次数,还有几个副本可以借
  38. for i in recorde:
  39. if i[4] != None: #归还时间不为空才继续执行,不然datetime.strptime会报错
  40. if today > datetime.strptime(i[4], '%Y-%m-%d') and i[6] == 0: #将字符串格式化为日期,避免输入错误
  41. print(f"副本{j - num},借书时间:{i[3]},归还期限{i[4]},已超期时间:{today - datetime.strptime(i[4],'%Y-%m-%d')}")
  42. else:
  43. print(f"副本{j - num},借书时间:{i[3]},归还期限{i[4]},未超期")
  44. j -= 1
  45. cur.close()
  46. # 显示所有书籍
  47. def showall():
  48. conn = getconn()
  49. cur = conn.cursor()
  50. recorde = cur.execute("select * from Book").fetchall()
  51. for re in recorde:
  52. print(re)
  53. querycopy(re[0])
  54. print()
  55. ##recorde=cur.execute("select * from Bookstate where Bookstate.number = Book.number").fetchall()
  56. cur.close()
  57. def getdata():
  58. number = input("请输入图书编号:")
  59. name = input("请输入图书名字:")
  60. author = input("请输入作者:")
  61. press = input("请输入出版社:")
  62. pubdate = input("请输入出版时间:")
  63. price = eval(input("请输入价格:"))
  64. return number, name, author, press, pubdate, price
  65. # 添加书籍
  66. def addbook():
  67. conn = getconn()
  68. cur = conn.cursor()
  69. records = getdata()
  70. sqlstr = "insert into Book (number,name,author,press,pubdate,price)" \
  71. "values(?,?,?,?,?,?)"
  72. # 每本书3个副本
  73. cur.execute(sqlstr, (records[0], records[1], records[2], records[3], records[4], records[5]))
  74. cur.execute(f"insert into Bookstate(id,number) values(1,{records[0]})")
  75. cur.execute(f"insert into Bookstate(id,number) values(2,{records[0]})")
  76. cur.execute(f"insert into Bookstate(id,number) values(3,{records[0]})")
  77. print("插入成功!")
  78. conn.commit()
  79. print(records[0], records[1], records[2], records[3], records[4], records[5])
  80. cur.close()
  81. # 查询图书信息
  82. def querybook():
  83. conn = getconn()
  84. cur = conn.cursor()
  85. op = input("按照编号查询,还是书名查询,请输入:")
  86. num = None
  87. name = None
  88. if op == "编号":
  89. num = input("请输入编号:")
  90. cur.execute("select * from Book where number=:num", {"num": num})
  91. record = cur.fetchall()
  92. for line in record:
  93. print(line)
  94. elif op == "书名":
  95. name = input("请输入书名:")
  96. cur.execute("select * from Book where name=:name", {"name": name})
  97. record = cur.fetchall()
  98. for line in record:
  99. print(line)
  100. cur.close()
  101. return [num, name]
  102. # 删除图书
  103. def delbook():
  104. conn = getconn()
  105. cur = conn.cursor()
  106. op = input("删除图书信息,你要输入编号还是书名:")
  107. if op == '编号':
  108. num = input("请输入编号:")
  109. cur.execute("select * from Book where number=:num", {"num": num})
  110. record = cur.fetchall()
  111. for line in record:
  112. print(line)
  113. cur.execute("delete from Book where number=:num", {"num": num})
  114. cur.execute("delete from Bookstate where number=:num", {"num": num})
  115. elif op == '书名':
  116. name = input("请输入书名:")
  117. cur.execute("select * from Book where name=:name", {"name": name})
  118. record = cur.fetchall()
  119. for line in record:
  120. print(line)
  121. cur.execute("delete from Bookstate where number in(select number from Book where name=:name)", {"name": name})
  122. cur.execute("delete from Book where name=:name", {"name": name})
  123. print("该图书删除成功!")
  124. conn.commit()
  125. cur.close()
  126. # 修改图书信息
  127. def udbook():
  128. conn = getconn()
  129. cur = conn.cursor()
  130. num = input("请输入图书编号:")
  131. row = cur.execute("select * from Book where number=?", (num,)).fetchall()
  132. if not row:
  133. print("没有该图书,请重试!")
  134. return 0
  135. print(row)
  136. print("请输入修改后的信息(图书名字不能修改):")
  137. record = getdata()
  138. sqlstr = "update Book set number=?,author=?,press=?,pubdate=?,price=? where number=?"
  139. cur.execute(sqlstr, (record[0], record[2], record[3], record[4], record[5], num))
  140. sqlstr = "update Bookstate set number=? where number=?"
  141. cur.execute(sqlstr, (record[0], num))
  142. print("修改成功")
  143. conn.commit()
  144. cur.close()
  145. # 判断操作是否继续执行
  146. def continueif():
  147. op = input("是否继续操作:y/n? 请输入:")
  148. if str.lower(op) == 'y':
  149. return 1
  150. elif str.lower(op) == 'n':
  151. return 0
  152. else:
  153. print("输入错误")
  154. def queryuser(): #管理员界面查询用户的借书状态
  155. username = input("请输入所要查询用户的编号:")
  156. querymybook(username)
  157. def managerlog():
  158. conn = getconn()
  159. cur = conn.cursor()
  160. username = input("请输入管理员账号:")
  161. password = input("请输入账号密码:")
  162. row = cur.execute("select * from manager where username=? and password=?", (username, password)).fetchone()
  163. if row is not None:
  164. print("登录成功!")
  165. return 1
  166. else:
  167. print("账号或者密码错误,登陆失败!")
  168. return 0
  169. def manager():
  170. flag = 1
  171. while flag == 1:
  172. line = "--------------------管理员界面------------------------------------"
  173. print(line)
  174. print("退出管理员界面:0 录入图书信息:1 删除图书信息:2 修改图书信息:3 ")
  175. print("查询某本图书信息和状态:4 查询任意用户借书状态:5 总览图书信息和状态:6")
  176. status = int(input("请输入选项:"))
  177. match status:
  178. case 0:
  179. break
  180. case 1:
  181. addbook()
  182. case 2:
  183. delbook()
  184. case 3:
  185. udbook()
  186. case 4:
  187. queryms()
  188. case 5:
  189. queryuser()
  190. case 6:
  191. showall()
  192. case _:
  193. print("没有该选项")
  194. flag = continueif()
  195. def log():
  196. conn = getconn()
  197. cur = conn.cursor()
  198. username = input("请输入登录账号:")
  199. password = input("请输入登录密码:")
  200. row = cur.execute("select * from Login where username=? and password=?", (username,password)).fetchone()
  201. if row is not None:
  202. print("登录成功!")
  203. return [1, username]
  204. else:
  205. print("账号或者密码错误,登陆失败!")
  206. return [0, None]
  207. def register():
  208. conn = getconn()
  209. cur = conn.cursor()
  210. username = input("请输入注册账号:")
  211. password = input("请输入注册密码:")
  212. cur.execute("insert into Login(username,password) values(?,?)", (username, password))
  213. print(f"注册成功!账号:{username},密码:{password} ")
  214. conn.commit()
  215. cur.close()
  216. def overtime(username): #判断是否有超期图书未归还
  217. conn = getconn()
  218. cur = conn.cursor()
  219. recorde = cur.execute("select * from Bookstate where username=?", (username,)).fetchall()
  220. if not recorde:
  221. return 0 #如果一条记录都没有,说明还未借书,也就没有超期图书
  222. today = datetime.today()
  223. for i in recorde:
  224. if i[4] != None: # 归还时间不为空才继续执行,不然datetime.strptime会报错
  225. if today > datetime.strptime(i[4], '%Y-%m-%d') and i[6] == 0: # 将字符串格式化为日期,避免输入错误
  226. recorde1 = cur.execute("select number,name from Book where number in "
  227. "(select number from Bookstate where username=? and number=?)", (i[7],i[1])).fetchall()
  228. print(f"图书编号:{recorde1[0][0]},图书名字:{recorde1[0][1]},副本{i[0]},借书时间:{i[3]},归还期限:{i[4]},"
  229. f"已超期时间:{today - datetime.strptime(i[4], '%Y-%m-%d')}")
  230. global overtime_flag
  231. overtime_flag = 0
  232. cur.close()
  233. def borrowbook(username): #user登录时的账号,在借书时使用此账号
  234. conn = getconn()
  235. cur = conn.cursor()
  236. #username = input("请输入账号进行借书:")
  237. op = overtime(username)
  238. if overtime_flag == 0:
  239. print(f"你有超期图书未归还,请归还后才能借阅")
  240. return 0 #有超期图书不能借书
  241. row = cur.execute("select count(*) as count1 from Bookstate where username=?", (username,)).fetchone()
  242. count = row[0]
  243. if count >= 2:
  244. print("对不起,一个账号一次只能借阅两本书,你已经达到数量上限")
  245. return 0
  246. op = input("输入图书编号或者名字,请选择:")
  247. if op == '编号':
  248. number = input("请输入图书编号:")
  249. recorde = cur.execute("select * from Book where number=?", (number,)).fetchall()
  250. print(f"图书信息:{recorde}")
  251. recorde = cur.execute("select * from Bookstate where number = ?", (number,)).fetchall()
  252. num = 0
  253. for i in recorde: # 计算图书还有多少副本可以借
  254. if i[6] == 1:
  255. num += 1
  256. if num == 0:
  257. print("该书已经没有副本可以借阅,请选择其他书籍")
  258. else:
  259. borrowtime = input("请输入借阅时间(yyyy-mm-dd):")
  260. returntime = input("请输入归还时间(yyyy-mm-dd):")
  261. if num == 3: #还有3个副本,从第一个开始借阅
  262. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
  263. "where id=1 and number=?"
  264. elif num == 2:
  265. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
  266. "where id=2 and number=?"
  267. elif num == 1:
  268. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
  269. "where id=3 and number=?"
  270. cur.execute(sqlstr, ("不在库", borrowtime, returntime, username, number))
  271. elif op == "名字":
  272. name = input("请输入图书名字:")
  273. recorde = cur.execute("select * from Book where name=?", (name,)).fetchall()
  274. print(f"图书信息:{recorde}")
  275. recorde = cur.execute("select * from Bookstate where number in(select number from Book where name=?)",
  276. (name,)).fetchall()
  277. number = recorde[0][1]
  278. num = 0
  279. for i in recorde: # 计算图书还有多少副本可以借
  280. if i[6] == 1:
  281. num += 1
  282. if num == 0:
  283. print("该书已经没有副本可以借阅,请选择其他书籍")
  284. else:
  285. borrowtime = input("请输入借阅时间(yyyy-mm-dd):")
  286. returntime = input("请输入归还时间(yyyy-mm-dd):")
  287. if num == 3: # 还有3个副本,从第一个开始借阅
  288. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
  289. "where id=1 and number=?"
  290. elif num == 2:
  291. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
  292. "where id=2 and number=?"
  293. elif num == 1:
  294. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=0,username=? " \
  295. "where id=3 and number=?"
  296. cur.execute(sqlstr, ("不在库", borrowtime, returntime, username, number))
  297. conn.commit()
  298. cur.close()
  299. def querymybook(username): #用户查询已借图书与状态
  300. conn = getconn()
  301. cur = conn.cursor()
  302. recorde = cur.execute("select * from Bookstate where username=?", (username,)).fetchall()
  303. #recorde1 = cur.execute("select * from Book where number in "
  304. # "(select number from Bookstate where username=?)", (recorde[0][7],)).fetchall()
  305. today = datetime.today()
  306. for i in recorde:
  307. if i[4] != None: # 归还时间不为空才继续执行,不然datetime.strptime会报错,且归还时间为空说明此书未借
  308. if today > datetime.strptime(i[4], '%Y-%m-%d') and i[6] == 0: # 将字符串格式化为日期,避免输入错误
  309. recorde1 = cur.execute("select * from Book where number in "
  310. "(select number from Bookstate where username=? and number=?)", (i[7],i[1])).fetchall()
  311. print(f"图书信息:图书编号:{recorde1[0][0]},图书名字:{recorde1[0][1]},作者:{recorde1[0][2]},"
  312. f"出版社:{recorde1[0][3]},出版时间:{recorde1[0][4]},价格:{recorde1[0][5]}")
  313. print(f",副本{i[0]},借书时间:{i[3]},归还期限:{i[4]},已超期时间:{today - datetime.strptime(i[4], '%Y-%m-%d')}\n")
  314. else:
  315. recorde1 = cur.execute("select * from Book where number in "
  316. "(select number from Bookstate where username=? and number=?)", (i[7],i[1])).fetchall()
  317. print(f"图书信息:图书编号:{recorde1[0][0]},图书名字:{recorde1[0][1]},作者:{recorde1[0][2]},"
  318. f"出版社:{recorde1[0][3]},出版时间:{recorde1[0][4]},价格:{recorde1[0][5]}")
  319. print(f",副本{i[0]},借书时间:{i[3]},归还期限:{i[4]},未超期\n")
  320. if not recorde:
  321. print("信息为空,还未借书")
  322. return 0
  323. def returnbook(username): #还书
  324. conn = getconn()
  325. cur = conn.cursor()
  326. print("你所借图书信息与状态")
  327. line = "-------------------------------------------"
  328. print(line)
  329. op = querymybook(username)
  330. print(line)
  331. if op == 0:
  332. return 0 #还没借书,直接退出
  333. number = input("请输入所还书籍的编号:")
  334. sqlstr = "update Bookstate set status=?, borrowtime=?,returntime=?,flag=?,username=? " \
  335. "where number=? and username=?"
  336. cur.execute(sqlstr, ('在库', None, None, 1, None, number, username))
  337. print("还书成功")
  338. conn.commit()
  339. cur.close()
  340. #recorde = cur.execute("select * from Bookstate where username=?", (username,)).fetchall()
  341. #recorde1 = cur.execute("select number,name from Book where number in "
  342. # "(select number from Bookstate where username=?)", (username,)).fetchall()
  343. def queryms(): #查询图书信息和状态
  344. conn = getconn()
  345. cur = conn.cursor()
  346. op = querybook() #查询图书信息
  347. if op[0] != None:
  348. querycopy(op[0]) #查询图书状态
  349. elif op[1] != None:
  350. num = cur.execute("select number from Book where name =?", (op[1],)).fetchall()
  351. querycopy(num[0][0]) #查询图书状态
  352. def user():
  353. flag = 1
  354. op = 2
  355. ch=2
  356. while ch == 2:
  357. ch = int(input("登录账户:1 注册账户:2 请输入选项:"))
  358. global overtime_flag
  359. overtime_flag = 2 #切换账号时需要把超期标志赋值,不然会影响后面的账号
  360. if ch == 1:
  361. op = log()
  362. elif ch == 2:
  363. register()
  364. if op[0] == 1:
  365. while flag == 1:
  366. line = "--------------------用户界面-----------------------"
  367. print(line)
  368. print("退出用户界面:0 借书:1 还书:2 ")
  369. print("查询图书信息和状态:3 查询自己已借图书信息和状态:4 ")
  370. status = int(input("请输入选项:"))
  371. match status:
  372. case 0:
  373. break
  374. case 1:
  375. borrowbook(op[1])
  376. case 2:
  377. returnbook(op[1])
  378. case 3:
  379. queryms()
  380. case 4:
  381. querymybook(op[1])
  382. case _:
  383. print("没有该选项")
  384. flag = continueif()
  385. else:
  386. print("欢迎下次光临!")
  387. if __name__ == "__main__":
  388. a = 1
  389. line = "--------------------主界面-----------------------"
  390. while a == 1:
  391. print(line)
  392. op = int(input("退出系统:0 管理员使用:1 用户使用:2 :"))
  393. if op == 1:
  394. ch = managerlog()
  395. if ch == 1:
  396. manager()
  397. elif op == 2:
  398. user()
  399. elif op == 0:
  400. break
  401. else:
  402. print("输入错误请重试!\n")

六、具体数据库结构设计截图

1.Book表

  

 2.Bookstate表

 3.Login表

 4.manager表

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/在线问答5/article/detail/747794
推荐阅读
相关标签
  

闽ICP备14008679号