Comment connecter Python à Mysql pour implémenter un système de prêt de livres

Structure des tables de la base de données

Nous avons besoin de trois tables ici, une table utilisateur, une table livre et une table d'emprunt. Notez que notre base de données s'appelle bbs (système d'emprunt de livres)

1. Table utilisateur

2 Table du livre

bookname : titre du livre
auteur : auteur
booknum : numéro du livre.
bookpress : éditeur
bookamoun : nombre de livres

3. Tableau d'emprunt

id : numéro d'emprunt
nom d'emprunt : emprunteur
borrowbook : livre d'emprunt
bookid : le numéro du livre est le même que celui de la table du livre. booknum
borrowamoun : Quantité d'emprunt
borrowdate : Date d'emprunt
borrowback : Date de retour

Programme Python

1 Programme principal : Système d'emprunt de livres.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. Informations sur la gestion des livres : 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. Opération de base de données : 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()

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!