数据库的表结构我们在这里需要三张表，一张用户表，一张图书表和一张借阅表。注意我们的数据库命名为bbs（book borrow system）
1.用户表
2.图书表
bookname:书名
author：作者
booknum：图书编号
bookpress:出版社
bookamoun:图书数量
3.借阅表
id:借阅号
borrowname:借阅人
borrowbook:借阅图书
bookid:图书编号同图书表booknum
borrowamoun:借阅数量
borrowdate:借阅日期
borrowback:归还日期
python程序1.主程序：图书借阅系统.py
# _*_ coding:utf-8 _*_import pymysqlimport db_eventimport book_managewhile true: print("欢迎使用图书借阅系统\ [1]登陆 [2]注册 [3]退出") choice = int(input("请输入您要进行的操作（数字）：")) if choice == 1: name = input("请输入用户名：") login_status=db_event.user_login(name) if login_status==1: book_manage.manage(name) else: print("登陆失败") continue elif choice==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])) elif choice==3: exit() else: print("无效操作！") continue
2.图书的管理信息：book_manage.py
import db_eventdef manage(name): while true: print("欢迎进入图书系统\n\ [1]查询图书 [2] 借阅图书 [3]捐赠图书 [4]归还图书 [5]退出") num = int(input('输入您的选择：')) if num == 1: db_event.book_select() elif num == 2 : chos=int(input("请选择[1]借阅 [2]续借 [3]查询借阅信息 [4]退出")) if chos==1: db_event.book_borrow(name) elif chos==2: db_event.borrow_again() elif chos==3: db_event.borrow_info_select(name) elif chos==4: continue else: print("无效操作") elif num == 3 : db_event.book_juanzeng() elif num == 4 : db_event.book_back() elif num == 5 : break else: print("无效输入！")
3.数据库的操作:db_event.py
# _*_ coding:utf-8 _*_import pymysqlimport randomimport stringdef user_login(name): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "select name,mima from user where name='%s'" %(name) cursor.execute(sql) results = cursor.fetchall() if results: res=results[0] for i in range(3): mima = input("请输入密码：") if mima == res[1]: print("登陆成功！") login_status = 1 break else: login_status=0 print("密码输入不正确!请重新输入") # print(login_status) if login_status == 1 : return login_status else: print("您已输入错误密码三次，无法登陆图书借阅系统，欢迎下次使用！") login_status = 0 return login_status else: login_status = 0 print("您输入的用户不存在！") return login_status db.close()#判断是否登陆成功，1为成功，0为不成功# login_status=user_login()# if login_status==1:# print("ok")# else:# print("no")#关闭数据库连接# curcor.close()# db.close()def user_create(): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() name=input("请输入姓名：") age=int(input("请输入年龄:")) sex=input("请输入性别[m]男 [w]女 ：") mima=input("为您的用户设置一个8位数密码：") sql = "insert into user values('%s',%s,'%s','%s')" %(name,age,sex,mima) cursor.execute(sql) db.commit() sql1="select * from user where name='%s'" %(name) cursor.execute(sql1) results=cursor.fetchone() return results db.close()#create_user=user_create()#print("用户创建成功，您创建的用户信息如下：/n\ # 姓名：%s 年龄：%d 性别：%s 密码：%s" %(create_user[0],create_user[1],create_user[2],create_user[3]))def book_info_select(x,y): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "select * from book where %s='%s'" %(x,y) cursor.execute(sql) results=cursor.fetchone() if results: print("书名：%s 作者：%s 书籍编号：%s 出版社：%s 剩余数量：%d " %(results[0],results[1],results[2],results[3],results[4])) else: print("没有您所要查询的图书") db.close()def book_select(): a = int(input("输入您要查询的图书关键信息\ [1]书名 [2]作者 [3]书籍号 [4]出版社")) b="" if a == 1 : b="bookname" name=input("请输入要查询的书名：") book_info_select(b,name) elif a == 2 : b="author" auth=input("请输入作者名：") book_info_select(b,auth) elif a == 3 : b="booknum" num=input("请输入书籍编号") book_info_select(b,num) elif a == 4 : b="bookpress" press=input("请输入出版社：") book_info_select(b,press) else: print("输入有误") book_select()def gen_code(len=8): code_str = string.ascii_letters + string.digits return ''.join(random.sample(code_str, len))def book_add(name,auth,press,amount): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() num=gen_code() sql = "insert into book values('%s','%s','%s','%s',%s)" %(name,auth,num,press,amount) sql1 = "select booknum from book" cursor.execute(sql1) res = cursor.fetchall() list=[] for i in res : list.append(i) try: while true: if num in list: gen_code() else: cursor.execute(sql) db.commit() print("图书捐赠成功，谢谢您！") break except: print("输入图书数目错误！") db.rollback() db.close()def book_update_add(name,auth,press,amount): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql="update book set bookamount=bookamount+%s where bookname='%s' and author='%s' and bookpress='%s'" %(amount,name,auth,press) try: cursor.execute(sql) db.commit() print("图书捐赠成功，谢谢您！") except: print("输入图书数目错误！") db.rollback() db.close()def book_juanzeng(): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() name=input("请输入您要捐赠的图书书名：") auth=input("请输入您要捐赠的图书作者：") press=input("请输入您要捐赠的图书的出版社：") amount = int(input("输入您要捐赠的数目：")) sql = "select * from book where bookname='%s'and author='%s' and bookpress='%s'" %(name,auth,press) cursor.execute(sql) results=cursor.fetchone() if results: book_update_add(name,auth,press,amount) else: book_add(name,auth,press,amount) db.close()def book_if_borrow(booknum,amount): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "select bookamount from book where booknum='%s'" %(booknum) cursor.execute(sql) res = cursor.fetchall() if res: if res[0][0] >= amount : #编号为booknum的书的数量还有，可以借 return true else: print("您所需要的编号为%s的书籍当前图书馆只有%d本，不满足您的需求" %(booknum,res[0][0])) return false else: print("查无此书，请确认您的书籍编号！") return false db.close()def book_borrow_after(amount,booknum): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "update book set bookamount=bookamount-%s where booknum='%s'" %(amount,booknum) cursor.execute(sql) db.commit() db.close()def borrow_add(name,booknum,amount): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() days = int(input("请输入您选择借阅的天数（不可超过365天）：")) sql = "insert into borrow values(null,'%s',(select bookname from book where booknum='%s'),'%s',%s,curdate(),date_add(curdate(),interval %s day))" %(name,booknum,booknum,amount,days) cursor.execute(sql) db.commit()def select_after_borrow(booknum,name): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql2 = "select * from borrow where bookid='%s' and borrowname='%s'" % (booknum, name) cursor.execute(sql2) return cursor.fetchall()def book_borrow(name): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() booknum=input("请输入您要借阅的图书编号：") amount=int(input("请输入您要借阅的书籍个数：")) sql1 = "select * from book where booknum='%s'" % (booknum) cursor.execute(sql1) result = cursor.fetchone() res = book_if_borrow(booknum,amount) if res: 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("借阅成功") while true: a=int(input("请输入您选择:[1]继续借阅 [2]退出")) if a == 1: book_borrow(name) break elif a == 2 : break else: print("无效操作") else: print("借阅失败") while true: a=int(input("请输入您选择:[1]继续借阅 [2]退出")) if a == 1: book_borrow(name) break elif a == 2 : break else: print("无效操作") db.close()def back_if_over(id): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "select * from borrow where backdate >= curdate() and id = %s" %(id) cursor.execute(sql) res=cursor.fetchall() if res: return true else: return false db.close()def book_back_update(id): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "update book set bookamount=bookamount+(select borrowamount from borrow where id = %s) where booknum=(select bookid from borrow where id = %s)" %(id,id) cursor.execute(sql) db.commit() db.close()def borrow_back_update(id): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "delete from borrow where id=%s" %(id) cursor.execute(sql) db.commit() db.close()def book_back(): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() while true: id = int(input("请输入您的借阅号：")) sql1 = "select * from borrow where id=%s" %(id) cursor.execute(sql1) info =cursor.fetchone() if info: 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]退出")) if choice == 1 : #判断是否逾期： if back_if_over(id): book_back_update(id) borrow_back_update(id) print("还书成功") break else: print("您已逾期，请联系管理员！") break elif choice == 2: continue elif choice == 3 : break else: print("无效输入") else: print("请输入正确的借阅号")def borrow_info_again(id,day): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql1 = "select * from borrow where id=%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()def borrow_update_again(id): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() a=int(input("请输入您的续借天数（不超过31天）:")) if a > 31 : print("您的借阅天数已超过系统权限，如要借阅，请联系管理员！") else: sql="update borrow set backdate=date_add(backdate,interval %s day) where id=%s" %(a,id) cursor.execute(sql) db.commit() db.close() return adef borrow_again(): id=int(input("输入您的借阅号：")) if back_if_over(id): day=borrow_update_again(id) borrow_info_again(id,day) print("续借成功") else: print("您已逾期，请先联系管理员再进行操作，谢谢！")def borrow_info_select(name): db = pymysql.connect("localhost", "ljz", "redhat", "bbs") cursor = db.cursor() sql = "select * from borrow where borrowname='%s'" %(name) cursor.execute(sql) res=cursor.fetchall() if res: for i in range(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实现图书借阅系统的详细内容。