I believe that we are learning python programming is absolutely inseparable from the database connection, then we will use python to connect to the database to achieve a simple book lending system. In fact, it is very simple, that is, in our program to add sql statements can be
Table structure of the database
We need three tables here, a user table, a book table and a borrow table. Note that our database is named bbs (book borrow system)
1. User table
2. Book List
bookname:Book name
author：author
booknum: book number
bookpress:Publisher
bookamoun: number of books
3. Borrowing form
id:Borrowing number
borrowname:Lenders
borrowbook:Borrowing books
bookid:book number with book table booknum
borrowamoun: number of borrowings
borrowdate: borrow date
borrowback:return date
Python program
1. Main program: book lending system.py
# _*_ coding:utf-8 _*_
import pymysql
import db_event
import book_manage
while True:
print("Welcome to the Book Lending System\
[1] Login [2] Register [3] Logout")
choice = int(input("Please enter the operation you want to perform (number):"))
if choice == 1:
name = input("Please enter user name:")
login_status=db_event.user_login(name)
if login_status==1:
book_manage.manage(name)
else:
print("Login failed.")
continue
elif choice==2:
create_user = db_event.user_create()
print("User created successfully, the user information you created is as follows:/n\
Name: %s Age: %d Gender: %s Password: %s" % (create_user[0], create_user[1], create_user[2], create_user[3]))
elif choice==3:
exit()
else:
print("Invalid operation!")
continue2. Book management information: book_manage.py
import db_event
def manage(name):
while True:
print("Welcome to the book system\n\\
[1] Search for books [2] Check out books [3] Donate books [4] Return books [5] Log out")
num = int(input('Enter your choice:'))
if num == 1:
db_event.book_select()
elif num == 2 :
chos=int(input("Please select [1] Borrow [2] Renew [3] Check Borrowing Information [4] Exit"))
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("Invalid operation")
elif num == 3 :
db_event.book_juanzeng()
elif num == 4 :
db_event.book_back()
elif num == 5 :
break
else:
print("Invalid input!")3. Database operations: db_event.py
# _*_ coding:utf-8 _*_
import pymysql
import random
import string
def user_login(name):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "SELECT name,mima FROM user WHERE name='%s'" %(name)
(sql)
results = ()
if results:
res=results[0]
for i in range(3):
mima = input("Please enter the password:")
if mima == res[1]:
print("Landing successful!")
login_status = 1
break
else:
login_status=0
print("The password was not entered correctly! Please re-enter.")
# print(login_status)
if login_status == 1 :
return login_status
else:
print("You have entered the wrong password three times, you are unable to log into the book lending system, welcome to use it next time!")
login_status = 0
return login_status
else:
login_status = 0
print("The user you entered does not exist!")
return login_status
()
#Judge whether the login is successful, 1 is successful, 0 is unsuccessful
# login_status=user_login()
# if login_status==1:
# print("ok")
# else:
# print("no")
# Close the database connection
# ()
# ()
def user_create():
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
name=input("Please enter your name:")
age=int(input("Please enter your age:"))
sex=input("Please enter gender [M] Male [W] Female:")
mima=input("Set an 8-digit password for your user:")
sql = "INSERT INTO user VALUES('%s',%s,'%s','%s')" %(name,age,sex,mima)
(sql)
()
sql1="SELECT * FROM user WHERE name='%s'" %(name)
(sql1)
results=()
return results
()
#create_user=user_create()
#print("User created successfully, you created the following user information:/n\
# Name: %s Age: %d Gender: %s Password: %s" %(create_user[0],create_user[1],create_user[2],create_user[3]))
def book_info_select(x,y):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "SELECT * FROM book WHERE %s='%s'" %(x,y)
(sql)
results=()
if results:
print("Title:%s Author:%s Book Number:%s Publisher:%s Remaining Quantity:%d " %(results[0],results[1],results[2],results[3],results[4]))
else:
print("There are no books available for your query.")
()
def book_select():
a = int(input("Enter the key information about the book you are looking for\
[1] Title [2] Author [3] Book number [4] Publisher"))
b=""
if a == 1 :
b="bookname"
name=input("Please enter the title of the book to be searched:")
book_info_select(b,name)
elif a == 2 :
b="author"
auth=input("Please enter the author's name:")
book_info_select(b,auth)
elif a == 3 :
b="booknum"
num=input("Please enter the book number.")
book_info_select(b,num)
elif a == 4 :
b="bookpress"
press=input("Please enter publisher:")
book_info_select(b,press)
else:
print("Input error.")
book_select()
def gen_code(len=8):
code_str = string.ascii_letters +
return ''.join((code_str, len))
def book_add(name,auth,press,amount):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
num=gen_code()
sql = "INSERT INTO book VALUES('%s','%s','%s','%s',%s)" %(name,auth,num,press,amount)
sql1 = "SELECT booknum FROM book"
(sql1)
res = ()
list=[]
for i in res :
(i)
try:
while True:
if num in list:
gen_code()
else:
(sql)
()
print("The book donation was a success, thank you!")
break
except:
print("Wrong number of books entered!")
()
()
def book_update_add(name,auth,press,amount):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql="UPDATE book SET bookamount=bookamount+%s WHERE bookname='%s' AND author='%s' AND bookpress='%s'" %(amount,name,auth,press)
try:
(sql)
()
print("The book donation was a success, thank you!")
except:
print("Wrong number of books entered!")
()
()
def book_juanzeng():
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
name=input("Please enter the title of the book you wish to donate:")
auth=input("Please enter the author of the book you wish to donate:")
press=input("Please enter the publisher of the book you are donating:")
amount = int(input("Enter the number you wish to donate:"))
sql = "SELECT * FROM book WHERE bookname='%s'AND author='%s' AND bookpress='%s'" %(name,auth,press)
(sql)
results=()
if results:
book_update_add(name,auth,press,amount)
else:
book_add(name,auth,press,amount)
()
def book_if_borrow(booknum,amount):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "SELECT bookamount FROM book WHERE booknum='%s'" %(booknum)
(sql)
res = ()
if res:
if res[0][0] >= amount :
#The number of books numbered booknum is still available for borrowing.
return True
else:
print("The book you need numbered %s is currently only %d available in the library and does not meet your needs" %(booknum,res[0][0]))
return False
else:
print("No book found, please confirm your book number!")
return False
()
def book_borrow_after(amount,booknum):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "UPDATE book SET bookamount=bookamount-%s WHERE booknum='%s'" %(amount,booknum)
(sql)
()
()
def borrow_add(name,booknum,amount):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
days = int(input("Please enter the number of days you choose to borrow (may not exceed 365 days):"))
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)
(sql)
()
def select_after_borrow(booknum,name):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql2 = "SELECT * FROM borrow WHERE bookid='%s' AND borrowname='%s'" % (booknum, name)
(sql2)
return ()
def book_borrow(name):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
booknum=input("Please enter the number of the book you wish to borrow:")
amount=int(input("Please enter the number of books you want to borrow:"))
sql1 = "SELECT * FROM book WHERE booknum='%s'" % (booknum)
(sql1)
result = ()
res = book_if_borrow(booknum,amount)
if res:
print("The book you want to borrow Book title: %s Author: %s Book number: %s Publisher: %s Currently remaining: %d books Remaining after borrowing: %d books" %(result[0],result[1],result[2],result[3],result[4],result[4]-amount))
book_borrow_after(amount,booknum)
#()
borrow_add(name,booknum,amount)
info=select_after_borrow(booknum,name)
print("Here is your information on the books you have borrowed, note the loan number, this will be your proof of return! \n\
Borrowing number: %d Borrower: %s Book borrowed: %s Book number: %s Quantity borrowed: %d Date borrowed: %s Date returned: %s" %(info[-1][0],info[-1][1],info[-1][2],info[-1][3],info[-1][4],info[-1][5],info[-1][6]))
print("Borrowed successfully.")
while True:
a=int(input("Please enter your choice: [1] Continue Borrowing [2] Withdraw."))
if a == 1:
book_borrow(name)
break
elif a == 2 :
break
else:
print("Invalid operation")
else:
print("Borrowing Failure")
while True:
a=int(input("Please enter your choice: [1] Continue Borrowing [2] Withdraw."))
if a == 1:
book_borrow(name)
break
elif a == 2 :
break
else:
print("Invalid operation")
()
def back_if_over(id):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "SELECT * FROM borrow WHERE backdate >= CURDATE() AND id = %s" %(id)
(sql)
res=()
if res:
return True
else:
return False
()
def book_back_update(id):
db = ("localhost", "ljz", "redhat", "bbs")
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)
(sql)
()
()
def borrow_back_update(id):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "DELETE FROM borrow WHERE id=%s" %(id)
(sql)
()
()
def book_back():
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
while True:
id = int(input("Please enter your loan number:"))
sql1 = "SELECT * FROM borrow WHERE id=%s" %(id)
(sql1)
info =()
if info:
print("Here is your information on the books you have borrowed, note the loan number, this will be your proof of return! \n\
Borrowing number: %d Borrower: %s Book borrowed: %s Book number: %s Quantity borrowed: %d Date borrowed: %s Date returned: %s" % (info[0], info[1], info[2], info[3], info[4], info[5], info[6]))
choice=int(input("Please confirm your return loan information: [1] Confirm [2] Return [3] Exit"))
if choice == 1 :
#Determine if it's overdue:
if back_if_over(id):
book_back_update(id)
borrow_back_update(id)
print("Returning the book was successful.")
break
else:
print("You are overdue, please contact an administrator!")
break
elif choice == 2:
continue
elif choice == 3 :
break
else:
print("Invalid input")
else:
print("Please enter the correct loan number.")
def borrow_info_again(id,day):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql1 = "SELECT * FROM borrow WHERE id=%s" % (id)
(sql1)
info = ()
print("The following is your borrowed book information:\n\
Borrowing number:%d Borrower:%s Renewal days:%d Book borrowed:%s Book number:%s Number of books borrowed:%d Initial borrowing date:%s Return date:%s" %(info[0], info[1],day,info[2], info[3], info[4], info[5], info[6]))
()
def borrow_update_again(id):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
a=int(input("Please enter the number of days you want to renew your loan (no more than 31 days):"))
if a > 31 :
print("Your borrowing days have exceeded the system privileges, if you want to borrow, please contact the administrator!")
else:
sql="UPDATE borrow SET backdate=DATE_ADD(backdate,INTERVAL %s DAY) WHERE id=%s" %(a,id)
(sql)
()
()
return a
def borrow_again():
id=int(input("Enter your loan number:"))
if back_if_over(id):
day=borrow_update_again(id)
borrow_info_again(id,day)
print("Renewal successful.")
else:
print("You are overdue, please contact an administrator before proceeding, thank you!")
def borrow_info_select(name):
db = ("localhost", "ljz", "redhat", "bbs")
cursor = ()
sql = "SELECT * FROM borrow WHERE borrowname='%s'" %(name)
(sql)
res=()
if res:
for i in range(len(res)):
print("The following is your %dth borrowed book information:\n\
Borrowing number:%d Borrower:%s Borrowed book:%s Book number:%s Number of books borrowed:%d Date borrowed:%s Date returned:%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("You have no books on loan.")
()The last thing I want to say is that I didn't add graphical or web here, so if anyone is interested in continuing this they can add what they like, I hope this helps.
This is the whole content of this article.