基于python+MySQL编写图书管理体系

惊落一身雪 · 2024-7-12 04:45:10

目录

一、功能
二、表
三、代码编写

一、功能

二、表

books表：

reader表：

三、代码编写

import pymysql
import datetime
# 建立数据库连接
connection = pymysql.connect(
host='localhost', # 数据库主机名
port=3306, # 数据库端口号，默认为3306
user='root', # 数据库用户名
passwd='123456', # 数据库密码
db='tushuguanlixitong', # 数据库名称
charset='utf8' # 字符编码
)
def query(sql, one=False):
cursor = connection.cursor()
cursor.execute(sql)
if one:
return cursor.fetchone()
else:
return cursor.fetchall()
def update(sql):
cursor = connection.cursor()
result = cursor.execute(sql)
# 提交事务
connection.commit()
return result
def select_book():
# 创建一个数据库游标对象
cursor = connection.cursor()
# 定义SQL查询语句，从图书信息表表中选择所有数据
sql = 'select*from books;'
# 执行SQL查询语句
cursor.execute(sql)
# 提交事务，确保数据被正确写入数据库
connection.commit()
# 从游标中获取查询结果，保存到data变量中
data = cursor.fetchall()
# 导入pandas库中的DataFrame类
from pandas import DataFrame
# 显示DataFrame的前5行数据
df = DataFrame(data, columns=['book_name', 'book_id', 'book_status', 'book_ISBN', 'author', 'press', 'borrower',
'loan_time'])
df.head()
print(df.head())
class Book:
def __init__(self, book_name, book_id, book_status, book_isbn, author, press):
self.book_name = book_name
self.author = author
self.book_id = book_id
self.book_status = book_status
self.book_ISBN = book_isbn
self.press = press
def add_book():
cursor = connection.cursor()
sql = ('INSERT INTO books (book_name,book_id,book_status,book_ISBN,author,press)'
'VALUES(%s,%s,%s,%s,%s,%s)')
print('请输入添加图书信息：')
book = Book(None, None, None, None, None, None)
book.book_name = input('请输入图书名：')
book.book_id = input('请输入图书编号：')
book.book_status = input('请输入图书状态：')
book.book_ISBN = input('请输入图书ISBN码：')
book.author = input('请输入图书作者：')
book.press = input('请输入图书出版社：')
values = (book.book_name, book.book_id, book.book_status, book.book_ISBN, book.author, book.press)
cursor.execute(sql, values)
connection.commit()
print('图书添加成功')
def delete_book():
cursor = connection.cursor()
book_id = input("输入需要删除的图书号：")
result = query("select * FROM books where book_id = {}".format(book_id), one=True)
if result:
print("图书信息：".format(result))
chooice = input("是否删除? 1.yes,2.no")
if chooice == '1':
update("DELETE FROM books where book_id = {}".format(book_id))
print("成功删除")
else:
print("放弃删除")
else:
print("未查询到相关书籍信息~")
num = input("继续删除请输入1，回车退回主菜单")
if num == "1":
delete_book()
def update_book():
cursor = connection.cursor()
book_id = input("输入需要删除的图书号：")
result = query("select * FROM books where book_id = {}".format(book_id), one=True)
if result:
print("图书信息：".format(result))
book_name = input("请输入修改书名：")
book_status = input("请输入图书状态：")
author = input("请输入修改作者：")
press = input("请输入修改出版社：")
update("update books set book_name = '{}',book_status = '{}',author = '{}',press = '{}' where book_id = {};"
.format(book_name, book_status, author, press, book_id))
print("更新成功")
else:
print("未查询到相关书籍信息~")
num = input("继续更新请输入1，回车退回主菜单")
if num == "1":
update_book()
def select_reader():
# 创建一个数据库游标对象
cursor = connection.cursor()
# 定义SQL查询语句，从图书信息表表中选择所有数据
sql = 'select*from reader;'
# 执行SQL查询语句
cursor.execute(sql)
# 提交事务，确保数据被正确写入数据库
connection.commit()
# 从游标中获取查询结果，保存到data变量中
data = cursor.fetchall()
# 导入pandas库中的DataFrame类
from pandas import DataFrame
# 显示DataFrame的前5行数据
df = DataFrame(data, columns=['姓名', '编号', '身份', '部门'])
df.head()
print(df.head())
# 查询读者名单
class Read:
def __init__(self, read_name, read_id, read_identity, read_department):
self.read_name = read_name
self.read_id = read_id
self.read_identity = read_identity
self.read_department = read_department
def add_reader():
cursor = connection.cursor()
sql = 'INSERT INTO reader (read_name,read_id,read_identity,read_department) VALUES(%s,%s,%s,%s)'
print('添加人员：')
read = Read(None, None, None, None)
read.read_name = input('请输入读者姓名')
read.read_id = input('请输入读者编号')
read.read_identity = input('请输入读者身份')
read.read_department = input('请输入读者所在部门')
values = (read.read_name, read.read_id, read.read_identity, read.read_department)
cursor.execute(sql, values)
connection.commit()
print('读者添加成功！')
def borrow_book():
cursor = connection.cursor()
book_id = input("请输入需要借阅的图书号：")
result = query("select * from books where book_id={};".format(book_id), one=True)
print(result)
if result:
if result[2] == "出借":
print("抱歉，该书已经借出！")
else:
while True:
borrower = input("请输入借阅者的名字：")
if result:
return_time = input("请输入还书的时间；")
update("update books set return_time='{}' where book_id={};".format(return_time, book_id))
if borrower:
update("update books set borrower='{}' where book_id={};".format(borrower, book_id))
update("update books set book_status='出借' where book_id={};".format(book_id))
print("图书借阅成功~")
break
else:
print("没有这个读者，请重新输入")
else:
print("未查询到相关书籍信息~")
num = input("继续借阅请输入1，回车退回主菜单")
if num == "1":
borrow_book()
def back_book():
cursor = connection.cursor()
book_id = input('请输入要归还的图书书号：')
result = query("select * from books where book_id={};".format(book_id), one=True)
if result:
if result[2] == '在架':
print("抱歉，该书在架请确认编号是否正确！")
else:
update("update books set borrower='' where book_id={};".format(book_id))
update("update books set book_status='在架' where book_id={};".format(book_id))
print("归还成功~")
else:
print("未查询到相关书籍信息~")
num = input("继续还书请输入1，回车退回主菜单")
if num == "1":
back_book()
def time():
cursor = connection.cursor()
now = datetime.datetime.now()
cursor .execute("SELECT * FROM books WHERE return_time < %s", (now,))
book_name = cursor.fetchall()
print("图书归还超期：")
print(book_name)
# 查询临期图书(距离当前时间一周内到期)
cursor.execute("SELECT * FROM books WHERE return_time BETWEEN %s AND %s",
(now, now + datetime.timedelta(days=10)))
due_soon_books = cursor.fetchall()
print("距离图书(到期还有10天):")
def menu(): # 图书管理系统菜单
while True:
print("""
图书管理系统
1.查询图书
2.增加图书
3.借阅图书
4.归还图书
5.修改图书
6.删除图书
7.导入读者名单
8.查看读者名单
9.超期和临期查询
10.退出系统
""")
choice = input('请选择：')
if choice == '1':
select_book()
elif choice == '2':
add_book()
elif choice == '3':
borrow_book()
elif choice == '4':
back_book()
elif choice == '5':
update_book()
elif choice == '6':
delete_book()
elif choice == '7':
add_reader()
elif choice == '8':
select_reader()
elif choice == '9':
time()
elif choice == '10':
print('欢迎下次使用~~~~~~~')
break
else:
print('请输入正确序号')
menu()

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

基于python+MySQL编写图书管理体系

0 个回复

快速回复

楼主热帖

标签云