Python如何連接Mysql實(shí)現(xiàn)圖書(shū)借閱系統(tǒng)
本篇內(nèi)容主要講解“Python如何連接MySQL實(shí)現(xiàn)圖書(shū)借閱系統(tǒng)”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“Python如何連接Mysql實(shí)現(xiàn)圖書(shū)借閱系統(tǒng)”吧!
創(chuàng)新互聯(lián)建站專(zhuān)注于企業(yè)網(wǎng)絡(luò)營(yíng)銷(xiāo)推廣、網(wǎng)站重做改版、普蘭店網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5開(kāi)發(fā)、商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為普蘭店等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。
數(shù)據(jù)庫(kù)的表結(jié)構(gòu)
我們?cè)谶@里需要三張表，一張用戶表，一張圖書(shū)表和一張借閱表。注意我們的數(shù)據(jù)庫(kù)命名為bbs（book borrow system）
1.用戶表
2.圖書(shū)表
bookname:書(shū)名
author：作者
booknum：圖書(shū)編號(hào)
bookpress:出版社
bookamoun:圖書(shū)數(shù)量
3.借閱表
id:借閱號(hào)
borrowname:借閱人
borrowbook:借閱圖書(shū)
bookid:圖書(shū)編號(hào)同圖書(shū)表booknum
borrowamoun:借閱數(shù)量
borrowdate:借閱日期
borrowback:歸還日期
Python程序
1.主程序：圖書(shū)借閱系統(tǒng).py
# _*_ coding:utf-8 _*_
import pymysql
import db_event
import book_manage
while True:
print("歡迎使用圖書(shū)借閱系統(tǒng)\
[1]登陸 [2]注冊(cè) [3]退出")
choice = int(input("請(qǐng)輸入您要進(jìn)行的操作（數(shù)字）："))
if choice == 1:
name = input("請(qǐng)輸入用戶名：")
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("用戶創(chuàng)建成功，您創(chuàng)建的用戶信息如下：/n\
姓名：%s 年齡：%d 性別：%s 密碼：%s" % (create_user[0], create_user[1], create_user[2], create_user[3]))
elif choice==3:
exit()
else:
print("無(wú)效操作！")
continue2.圖書(shū)的管理信息：book_manage.py
import db_event
def manage(name):
while True:
print("歡迎進(jìn)入圖書(shū)系統(tǒng)\n\
[1]查詢圖書(shū) [2] 借閱圖書(shū) [3]捐贈(zèng)圖書(shū) [4]歸還圖書(shū) [5]退出")
num = int(input('輸入您的選擇：'))
if num == 1:
db_event.book_select()
elif num == 2 :
chos=int(input("請(qǐng)選擇[1]借閱 [2]續(xù)借 [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("無(wú)效操作")
elif num == 3 :
db_event.book_juanzeng()
elif num == 4 :
db_event.book_back()
elif num == 5 :
break
else:
print("無(wú)效輸入！")3.數(shù)據(jù)庫(kù)的操作: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("請(qǐng)輸入密碼：")
if mima == res[1]:
print("登陸成功！")
login_status = 1
break
else:
login_status=0
print("密碼輸入不正確!請(qǐng)重新輸入")
# print(login_status)
if login_status == 1 :
return login_status
else:
print("您已輸入錯(cuò)誤密碼三次，無(wú)法登陸圖書(shū)借閱系統(tǒng)，歡迎下次使用！")
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")
#關(guān)閉數(shù)據(jù)庫(kù)連接
# curcor.close()
# db.close()
def user_create():
db = pymysql.connect("localhost", "ljz", "redhat", "bbs")
cursor = db.cursor()
name=input("請(qǐng)輸入姓名：")
age=int(input("請(qǐng)輸入年齡:"))
sex=input("請(qǐng)輸入性別[M]男 [W]女 ：")
mima=input("為您的用戶設(shè)置一個(gè)8位數(shù)密碼：")
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("用戶創(chuàng)建成功，您創(chuàng)建的用戶信息如下：/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("書(shū)名：%s 作者：%s 書(shū)籍編號(hào)：%s 出版社：%s 剩余數(shù)量：%d " %(results[0],results[1],results[2],results[3],results[4]))
else:
print("沒(méi)有您所要查詢的圖書(shū)")
db.close()
def book_select():
a = int(input("輸入您要查詢的圖書(shū)關(guān)鍵信息\
[1]書(shū)名 [2]作者 [3]書(shū)籍號(hào) [4]出版社"))
b=""
if a == 1 :
b="bookname"
name=input("請(qǐng)輸入要查詢的書(shū)名：")
book_info_select(b,name)
elif a == 2 :
b="author"
auth=input("請(qǐng)輸入作者名：")
book_info_select(b,auth)
elif a == 3 :
b="booknum"
num=input("請(qǐng)輸入書(shū)籍編號(hào)")
book_info_select(b,num)
elif a == 4 :
b="bookpress"
press=input("請(qǐng)輸入出版社：")
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("圖書(shū)捐贈(zèng)成功，謝謝您！")
break
except:
print("輸入圖書(shū)數(shù)目錯(cuò)誤！")
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("圖書(shū)捐贈(zèng)成功，謝謝您！")
except:
print("輸入圖書(shū)數(shù)目錯(cuò)誤！")
db.rollback()
db.close()
def book_juanzeng():
db = pymysql.connect("localhost", "ljz", "redhat", "bbs")
cursor = db.cursor()
name=input("請(qǐng)輸入您要捐贈(zèng)的圖書(shū)書(shū)名：")
auth=input("請(qǐng)輸入您要捐贈(zèng)的圖書(shū)作者：")
press=input("請(qǐng)輸入您要捐贈(zèng)的圖書(shū)的出版社：")
amount = int(input("輸入您要捐贈(zèng)的數(shù)目："))
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 :
#編號(hào)為booknum的書(shū)的數(shù)量還有，可以借
return True
else:
print("您所需要的編號(hào)為%s的書(shū)籍當(dāng)前圖書(shū)館只有%d本，不滿足您的需求" %(booknum,res[0][0]))
return False
else:
print("查無(wú)此書(shū)，請(qǐng)確認(rèn)您的書(shū)籍編號(hào)！")
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("請(qǐng)輸入您選擇借閱的天數(shù)（不可超過(guò)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("請(qǐng)輸入您要借閱的圖書(shū)編號(hào)：")
amount=int(input("請(qǐng)輸入您要借閱的書(shū)籍個(gè)數(shù)："))
sql1 = "SELECT * FROM book WHERE booknum='%s'" % (booknum)
cursor.execute(sql1)
result = cursor.fetchone()
res = book_if_borrow(booknum,amount)
if res:
print("您要借閱的書(shū)籍書(shū)名：%s 作者：%s 書(shū)籍編號(hào)：%s 出版社: %s 當(dāng)前剩余：%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("以下是您的借閱圖書(shū)信息，注意借閱號(hào)，這將是您還書(shū)的憑證！\n\
借閱號(hào)：%d 借閱人：%s 借閱圖書(shū)：%s 圖書(shū)編號(hào)：%s 借閱數(shù)量：%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("請(qǐng)輸入您選擇:[1]繼續(xù)借閱 [2]退出"))
if a == 1:
book_borrow(name)
break
elif a == 2 :
break
else:
print("無(wú)效操作")
else:
print("借閱失敗")
while True:
a=int(input("請(qǐng)輸入您選擇:[1]繼續(xù)借閱 [2]退出"))
if a == 1:
book_borrow(name)
break
elif a == 2 :
break
else:
print("無(wú)效操作")
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("請(qǐng)輸入您的借閱號(hào)："))
sql1 = "SELECT * FROM borrow WHERE id=%s" %(id)
cursor.execute(sql1)
info =cursor.fetchone()
if info:
print("以下是您的借閱圖書(shū)信息，注意借閱號(hào)，這將是您還書(shū)的憑證！\n\
借閱號(hào)：%d 借閱人：%s 借閱圖書(shū)：%s 圖書(shū)編號(hào)：%s 借閱數(shù)量：%d 借閱日期：%s 歸還日期：%s" % (info[0], info[1], info[2], info[3], info[4], info[5], info[6]))
choice=int(input("請(qǐng)確認(rèn)您的歸還借書(shū)信息：[1]確認(rèn) [2]返回 [3]退出"))
if choice == 1 :
#判斷是否逾期：
if back_if_over(id):
book_back_update(id)
borrow_back_update(id)
print("還書(shū)成功")
break
else:
print("您已逾期，請(qǐng)聯(lián)系管理員！")
break
elif choice == 2:
continue
elif choice == 3 :
break
else:
print("無(wú)效輸入")
else:
print("請(qǐng)輸入正確的借閱號(hào)")
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("以下是您的借閱圖書(shū)信息:\n\
借閱號(hào)：%d 借閱人：%s 續(xù)借天數(shù)：%d 借閱圖書(shū)：%s 圖書(shū)編號(hào)：%s 借閱數(shù)量：%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("請(qǐng)輸入您的續(xù)借天數(shù)（不超過(guò)31天）:"))
if a > 31 :
print("您的借閱天數(shù)已超過(guò)系統(tǒng)權(quán)限，如要借閱，請(qǐng)聯(lián)系管理員！")
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("輸入您的借閱號(hào)："))
if back_if_over(id):
day=borrow_update_again(id)
borrow_info_again(id,day)
print("續(xù)借成功")
else:
print("您已逾期，請(qǐng)先聯(lián)系管理員再進(jìn)行操作，謝謝！")
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條借閱圖書(shū)信息:\n\
借閱號(hào)：%d 借閱人：%s 借閱圖書(shū)：%s 圖書(shū)編號(hào)：%s 借閱數(shù)量：%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("您沒(méi)有借閱圖書(shū)")
db.close()到此，相信大家對(duì)“Python如何連接Mysql實(shí)現(xiàn)圖書(shū)借閱系統(tǒng)”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
當(dāng)前名稱(chēng)：Python如何連接Mysql實(shí)現(xiàn)圖書(shū)借閱系統(tǒng)
網(wǎng)頁(yè)路徑：http://aaarwkj.com/article30/iipcso.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、外貿(mào)網(wǎng)站建設(shè)、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)公司、做網(wǎng)站、外貿(mào)建站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 品牌網(wǎng)站制作解決方案七步走 2021-12-19
- 高端品牌網(wǎng)站制作策劃方案 2021-10-09
- 品牌網(wǎng)站制作有哪些重要注意事項(xiàng)？ 2016-08-20
- 如何做好品牌網(wǎng)站制作 2021-11-16
- 品牌網(wǎng)站制作解決方案 2016-09-12
- 如何打響品牌網(wǎng)站制作的知名度 2021-12-11
- 品牌網(wǎng)站制作方案要怎么做 2021-08-28
- 品牌網(wǎng)站制作與普通網(wǎng)站制作的區(qū)別 2021-09-02
- 品牌網(wǎng)站制作常見(jiàn)的布局方式 2022-03-22
- 品牌網(wǎng)站制作好方法好步驟？ 2021-06-03
- 深圳品牌網(wǎng)站制作營(yíng)銷(xiāo)重點(diǎn) 2021-09-28
- 企業(yè)品牌網(wǎng)站制作要注重哪些問(wèn)題? 2023-04-18