本篇内容主要讲解“Python如何连接Mysql实现图书借阅系统”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Python如何连接Mysql实现图书借阅系统”吧!

我们在这里需要三张表，一张用户表，一张图书表和一张借阅表。注意我们的数据库命名为bbs（book borrow system）

1.用户表

2.图书表

bookname:书名
author：作者
booknum：图书编号
bookpress:出版社
bookamoun:图书数量

3.借阅表

id:借阅号
borrowname:借阅人
borrowbook:借阅图书
bookid:图书编号同图书表booknum
borrowamoun:借阅数量
borrowdate:借阅日期
borrowback:归还日期

1.主程序：图书借阅系统.py

#_*_coding:utf-8_*_importpymysqlimportdb_eventimportbook_managewhileTrue:print("欢迎使用图书借阅系统\[1]登陆[2]注册[3]退出")choice=int(input("请输入您要进行的操作（数字）："))ifchoice==1:name=input("请输入用户名：")login_status=db_event.user_login(name)iflogin_status==1:book_manage.manage(name)else:print("登陆失败")continueelifchoice==2:create_user=db_event.user_create()print("用户创建成功，您创建的用户信息如下：/n\姓名：%s年龄：%d性别：%s密码：%s"%(create_user[0],create_user[1],create_user[2],create_user[3]))elifchoice==3:exit()else:print("无效操作！")continue

2.图书的管理信息：book_manage.py

importdb_eventdefmanage(name):whileTrue:print("欢迎进入图书系统\n\[1]查询图书[2]借阅图书[3]捐赠图书[4]归还图书[5]退出")num=int(input('输入您的选择：'))ifnum==1:db_event.book_select()elifnum==2:chos=int(input("请选择[1]借阅[2]续借[3]查询借阅信息[4]退出"))ifchos==1:db_event.book_borrow(name)elifchos==2:db_event.borrow_again()elifchos==3:db_event.borrow_info_select(name)elifchos==4:continueelse:print("无效操作")elifnum==3:db_event.book_juanzeng()elifnum==4:db_event.book_back()elifnum==5:breakelse:print("无效输入！")

3.数据库的操作:db_event.py

#_*_coding:utf-8_*_importpymysqlimportrandomimportstringdefuser_login(name):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="SELECTname,mimaFROMuserWHEREname='%s'"%(name)cursor.execute(sql)results=cursor.fetchall()ifresults:res=results[0]foriinrange(3):mima=input("请输入密码：")ifmima==res[1]:print("登陆成功！")login_status=1breakelse:login_status=0print("密码输入不正确!请重新输入")#print(login_status)iflogin_status==1:returnlogin_statuselse:print("您已输入错误密码三次，无法登陆图书借阅系统，欢迎下次使用！")login_status=0returnlogin_statuselse:login_status=0print("您输入的用户不存在！")returnlogin_statusdb.close()#判断是否登陆成功，1为成功，0为不成功#login_status=user_login()#iflogin_status==1:#print("ok")#else:#print("no")#关闭数据库连接#curcor.close()#db.close()defuser_create():db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()name=input("请输入姓名：")age=int(input("请输入年龄:"))sex=input("请输入性别[M]男[W]女：")mima=input("为您的用户设置一个8位数密码：")sql="INSERTINTOuserVALUES('%s',%s,'%s','%s')"%(name,age,sex,mima)cursor.execute(sql)db.commit()sql1="SELECT*FROMuserWHEREname='%s'"%(name)cursor.execute(sql1)results=cursor.fetchone()returnresultsdb.close()#create_user=user_create()#print("用户创建成功，您创建的用户信息如下：/n\#姓名：%s年龄：%d性别：%s密码：%s"%(create_user[0],create_user[1],create_user[2],create_user[3]))defbook_info_select(x,y):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="SELECT*FROMbookWHERE%s='%s'"%(x,y)cursor.execute(sql)results=cursor.fetchone()ifresults:print("书名：%s作者：%s书籍编号：%s出版社：%s剩余数量：%d"%(results[0],results[1],results[2],results[3],results[4]))else:print("没有您所要查询的图书")db.close()defbook_select():a=int(input("输入您要查询的图书关键信息\[1]书名[2]作者[3]书籍号[4]出版社"))b=""ifa==1:b="bookname"name=input("请输入要查询的书名：")book_info_select(b,name)elifa==2:b="author"auth=input("请输入作者名：")book_info_select(b,auth)elifa==3:b="booknum"num=input("请输入书籍编号")book_info_select(b,num)elifa==4:b="bookpress"press=input("请输入出版社：")book_info_select(b,press)else:print("输入有误")book_select()defgen_code(len=8):code_str=string.ascii_letters+string.digitsreturn''.join(random.sample(code_str,len))defbook_add(name,auth,press,amount):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()num=gen_code()sql="INSERTINTObookVALUES('%s','%s','%s','%s',%s)"%(name,auth,num,press,amount)sql1="SELECTbooknumFROMbook"cursor.execute(sql1)res=cursor.fetchall()list=[]foriinres:list.append(i)try:whileTrue:ifnuminlist:gen_code()else:cursor.execute(sql)db.commit()print("图书捐赠成功，谢谢您！")breakexcept:print("输入图书数目错误！")db.rollback()db.close()defbook_update_add(name,auth,press,amount):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="UPDATEbookSETbookamount=bookamount+%sWHEREbookname='%s'ANDauthor='%s'ANDbookpress='%s'"%(amount,name,auth,press)try:cursor.execute(sql)db.commit()print("图书捐赠成功，谢谢您！")except:print("输入图书数目错误！")db.rollback()db.close()defbook_juanzeng():db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()name=input("请输入您要捐赠的图书书名：")auth=input("请输入您要捐赠的图书作者：")press=input("请输入您要捐赠的图书的出版社：")amount=int(input("输入您要捐赠的数目："))sql="SELECT*FROMbookWHEREbookname='%s'ANDauthor='%s'ANDbookpress='%s'"%(name,auth,press)cursor.execute(sql)results=cursor.fetchone()ifresults:book_update_add(name,auth,press,amount)else:book_add(name,auth,press,amount)db.close()defbook_if_borrow(booknum,amount):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="SELECTbookamountFROMbookWHEREbooknum='%s'"%(booknum)cursor.execute(sql)res=cursor.fetchall()ifres:ifres[0][0]>=amount:#编号为booknum的书的数量还有，可以借returnTrueelse:print("您所需要的编号为%s的书籍当前图书馆只有%d本，不满足您的需求"%(booknum,res[0][0]))returnFalseelse:print("查无此书，请确认您的书籍编号！")returnFalsedb.close()defbook_borrow_after(amount,booknum):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="UPDATEbookSETbookamount=bookamount-%sWHEREbooknum='%s'"%(amount,booknum)cursor.execute(sql)db.commit()db.close()defborrow_add(name,booknum,amount):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()days=int(input("请输入您选择借阅的天数（不可超过365天）："))sql="INSERTINTOborrowVALUES(NULL,'%s',(SELECTbooknameFROMbookWHEREbooknum='%s'),'%s',%s,CURDATE(),DATE_ADD(CURDATE(),INTERVAL%sDAY))"%(name,booknum,booknum,amount,days)cursor.execute(sql)db.commit()defselect_after_borrow(booknum,name):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql2="SELECT*FROMborrowWHEREbookid='%s'ANDborrowname='%s'"%(booknum,name)cursor.execute(sql2)returncursor.fetchall()defbook_borrow(name):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()booknum=input("请输入您要借阅的图书编号：")amount=int(input("请输入您要借阅的书籍个数："))sql1="SELECT*FROMbookWHEREbooknum='%s'"%(booknum)cursor.execute(sql1)result=cursor.fetchone()res=book_if_borrow(booknum,amount)ifres:print("您要借阅的书籍书名：%s作者：%s书籍编号：%s出版社:%s当前剩余：%d本借后剩余：%d本"%(result[0],result[1],result[2],result[3],result[4],result[4]-amount))book_borrow_after(amount,booknum)#db.commit()borrow_add(name,booknum,amount)info=select_after_borrow(booknum,name)print("以下是您的借阅图书信息，注意借阅号，这将是您还书的凭证！\n\借阅号：%d借阅人：%s借阅图书：%s图书编号：%s借阅数量：%d借阅日期：%s归还日期：%s"%(info[-1][0],info[-1][1],info[-1][2],info[-1][3],info[-1][4],info[-1][5],info[-1][6]))print("借阅成功")whileTrue:a=int(input("请输入您选择:[1]继续借阅[2]退出"))ifa==1:book_borrow(name)breakelifa==2:breakelse:print("无效操作")else:print("借阅失败")whileTrue:a=int(input("请输入您选择:[1]继续借阅[2]退出"))ifa==1:book_borrow(name)breakelifa==2:breakelse:print("无效操作")db.close()defback_if_over(id):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="SELECT*FROMborrowWHEREbackdate>=CURDATE()ANDid=%s"%(id)cursor.execute(sql)res=cursor.fetchall()ifres:returnTrueelse:returnFalsedb.close()defbook_back_update(id):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="UPDATEbookSETbookamount=bookamount+(SELECTborrowamountFROMborrowWHEREid=%s)WHEREbooknum=(SELECTbookidFROMborrowWHEREid=%s)"%(id,id)cursor.execute(sql)db.commit()db.close()defborrow_back_update(id):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="DELETEFROMborrowWHEREid=%s"%(id)cursor.execute(sql)db.commit()db.close()defbook_back():db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()whileTrue:id=int(input("请输入您的借阅号："))sql1="SELECT*FROMborrowWHEREid=%s"%(id)cursor.execute(sql1)info=cursor.fetchone()ifinfo:print("以下是您的借阅图书信息，注意借阅号，这将是您还书的凭证！\n\借阅号：%d借阅人：%s借阅图书：%s图书编号：%s借阅数量：%d借阅日期：%s归还日期：%s"%(info[0],info[1],info[2],info[3],info[4],info[5],info[6]))choice=int(input("请确认您的归还借书信息：[1]确认[2]返回[3]退出"))ifchoice==1:#判断是否逾期：ifback_if_over(id):book_back_update(id)borrow_back_update(id)print("还书成功")breakelse:print("您已逾期，请联系管理员！")breakelifchoice==2:continueelifchoice==3:breakelse:print("无效输入")else:print("请输入正确的借阅号")defborrow_info_again(id,day):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql1="SELECT*FROMborrowWHEREid=%s"%(id)cursor.execute(sql1)info=cursor.fetchone()print("以下是您的借阅图书信息:\n\借阅号：%d借阅人：%s续借天数：%d借阅图书：%s图书编号：%s借阅数量：%d初始借阅日期：%s归还日期：%s"%(info[0],info[1],day,info[2],info[3],info[4],info[5],info[6]))db.close()defborrow_update_again(id):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()a=int(input("请输入您的续借天数（不超过31天）:"))ifa>31:print("您的借阅天数已超过系统权限，如要借阅，请联系管理员！")else:sql="UPDATEborrowSETbackdate=DATE_ADD(backdate,INTERVAL%sDAY)WHEREid=%s"%(a,id)cursor.execute(sql)db.commit()db.close()returnadefborrow_again():id=int(input("输入您的借阅号："))ifback_if_over(id):day=borrow_update_again(id)borrow_info_again(id,day)print("续借成功")else:print("您已逾期，请先联系管理员再进行操作，谢谢！")defborrow_info_select(name):db=pymysql.connect("localhost","ljz","redhat","bbs")cursor=db.cursor()sql="SELECT*FROMborrowWHEREborrowname='%s'"%(name)cursor.execute(sql)res=cursor.fetchall()ifres:foriinrange(len(res)):print("以下是您的第%d条借阅图书信息:\n\借阅号：%d借阅人：%s借阅图书：%s图书编号：%s借阅数量：%d借阅日期：%s归还日期：%s"%(i+1,res[i][0],res[i][1],res[i][2],res[i][3],res[i][4],res[i][5],res[i][6]))else:print("您没有借阅图书")db.close()

到此，相信大家对“Python如何连接Mysql实现图书借阅系统”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！