共计 9876 个字符，预计需要花费 25 分钟才能阅读完成。
本篇内容主要讲解“Python 如何连接 Mysql 实现图书借阅系统”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“Python 如何连接 Mysql 实现图书借阅系统”吧!
数据库的表结构
我们在这里需要三张表，一张用户表，一张图书表和一张借阅表。注意我们的数据库命名为 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
[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()到此，相信大家对“Python 如何连接 Mysql 实现图书借阅系统”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
正文完
