Python連接Mysql實現圖書借閱系統
相信大傢在學習python編程時絕對離不開數據庫的連接,那麼我們就用python來連接數據庫實現一個簡單的圖書借閱系統。其實也很簡單,就是在我們的程序中加入sql語句即可
數據庫的表結構
我們在這裡需要三張表,一張用戶表,一張圖書表和一張借閱表。註意我們的數據庫命名為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 pymysql import db_event import book_manage while 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_event def 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 pymysql import random import string def 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 a def 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()
最後小編想說的是我在這裡沒有加入圖形化或者web,如果有人有興趣繼續做下去的話可以添加自己喜歡的東西,希望對大傢有幫助。
以上就是本文的全部內容,希望對大傢的學習有所幫助,也希望大傢多多支持WalkonNet。
推薦閱讀:
- python mysql實現學生成績管理系統
- 教你用python實現一個無界面的小型圖書管理系統
- Python通過pymysql調用MySQL進行增刪改移查
- 教你怎麼用Python操作MySql數據庫
- Python基礎之操作MySQL數據庫