Python怎么连接Mysql实现图书借阅系统

数据库的表结构

我们在这里需要三张表，一张用户表，一张图书表和一张借阅表。注意我们的数据库命名为bbs（book borrow system）

1.用户表

2.图书表

立即学习“Python免费学习笔记（深入）”；

bookname:书名author：作者booknum：图书编号bookpress:出版社bookamoun:图书数量

3.借阅表

九歌

九歌–人工智能诗歌写作系统

322 查看详情

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实现图书借阅系统的详细内容，更多请关注创想鸟其它相关文章！