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 www.cppcns.com= "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 = cuwww.cppcns.comrsor.fetchone()
  res = book_if_borrow(booknum,amount)
  if res:
    print("您要借阅的书籍书名：%s 作者：%s 书籍编号：%s 出版社: %s 当前剩余：%d本 借后剩余：%d本" %(result[0],result[1],result[2],result[3]http://www.cppcns.com,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", oVExBWQhTH"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，如果有人有兴趣继续做下去的话可以添加自己喜欢的东西，希望对大家有帮助。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。