一、需求了解
功能模块
图书信息
二、环境准备
安装mysql数据库
参考文章:
MySQL数据库压缩版本安装与配置
MySQL msi版本下载安装图文教程
创建数据库表
创建数据库
CREATE DATABASE bookmanage;
使用数据库
use bookmanage;
创建表
create table books(
id int unsigned primary key auto_increment not null,
name varchar(20) default “”,
position varchar(40) default “”,
status enum(‘在库\’, ‘出借\’) default ‘在库\’,
borrower varchar(20) default “”
);
插入数据
insert into books(name, position) value (‘python从入门到放弃\’, ‘A-1-1\’);
查询数据
select * from books where id=2;
修改数据
update books set name=‘python\’;
删除数据
delete from book where id=3;
三、代码实现
引入pymysql模块
安装pymysql
命令：pip install pymysql
封装操作数据库模块
# -*- coding: utf-8 -*-
\"\"\"
===============================
@Time : 2021/5/18 15:56
@Author : flora.chen
@FileName: handle_mysql.py
@Software: PyCharm
===============================
\"\"\"
import pymysql
class MysqlDB:
\"\"\"
操作mysql数据库
\"\"\"
def __init__(self, host, user, pwd, database=None, port=3306):
\"\"\"
初始化数据库链接
:param host: 主机地址
:param user: 用户名
:param pwd: 密码
:param database: 数据库名称，默认为空
:param port: 端口号，默认3306
\"\"\"
self.conn = pymysql.connect(
host=host,
user=user,
password=pwd,
database=database,
port=port,
cursorclass=pymysql.cursors.DictCursor
)
# 创建一个游标对象
self.cur = self.conn.cursor()
def update(self, sql):
\"\"\"
进行增删改操作
:param sql: 需要执行的SQL
:return:
\"\"\"
# 执行SQL
result = self.cur.execute(sql)
# 提交事务
self.conn.commit()
return result
def query(self, sql, one=False):
\"\"\"
进行查询操作
:param one: 判断是要返回所有查询数据还是第一条，默认是所有
:param sql: 要执行的SQL
:return:
\"\"\"
# 执行SQL
self.cur.execute(sql)
if one:
return self.cur.fetchone()
else:
return self.cur.fetchall()
def close(self):
\"\"\"
断开游标，关闭数据库连接
:return:
\"\"\"
self.cur.close()
self.conn.close()
if __name__ == \"__main__\":
db = MysqlDB(host=\"localhost\", user=\"root\", pwd=\"root\")
print(db.query(\"select * from bookmanage.books\"))
# db.update(\"insert into bookmanage.books(name, position) value (\'python从入门到放弃\', \'A-1-1\');\")
图案管理系统后台实现
# -*- coding: utf-8 -*-
\"\"\"
===============================
@Time : 2021/5/18 16:39
@Author : flora.chen
@FileName: bookmanager.py
@Software: PyCharm
===============================
\"\"\"
from handle_mysql import MysqlDB
db = MysqlDB(host=\"localhost\", database=\"bookmanage\", user=\"root\", pwd=\"root\")
class BookManage:
\"\"\"
图书管理系统
\"\"\"
@staticmethod
def print_menu():
\"\"\"
菜单打印
:return:
\"\"\"
print(\"---------------------菜单-------------------------\")
print(\"[1]: 添加图书\")
print(\"[2]: 修改图书\")
print(\"[3]: 删除图书\")
print(\"[4]: 查询图书\")
print(\"[5]: 图书列表\")
print(\"[6]: 出借图书\")
print(\"[7]: 归还图书\")
print(\"[8]: 退出\")
def add_book(self):
\"\"\"
[1]: 添加图书
:return:
\"\"\"
print(\"****************添加图书****************\")
name = input(\"请输入书名：\")
position = input(\"请输入图书位置：\")
if name and position:
db.update(\"insert into books(name, position) value (\'{}\', \'{}\');\".format(name, position))
print(\"图书添加成功\")
else:
print(\"书名或者图书位置不能为空，请重新输入！\")
num = input(\"继续添加请输入1， 回车退回主菜单\")
if num == \"1\":
self.add_book()
def update_book(self):
\"\"\"
[2]: 修改图书
:return:
\"\"\"
print(\"****************修改图书****************\")
book_id = input(\"请输入需要修改的图书ID：\")
result = db.query(\"select * from books where id={};\".format(book_id), one=True)
if result:
print(\"当前数据为：{}\".format(result))
name = input(\"重新输入书名，不修改输入回车：\") or result[\"name\"]
position = input(\"重新输入位置，不修改输入回车：\") or result[\"position\"]
db.update(\"update books set name=\'{}\', position=\'{}\' where id={};\".format(name, position, book_id))
print(\"修改成功\")
else:
print(\"您输入的图书ID不存在，请重新输入~\")
num = input(\"继续修改请输入1， 回车退回主菜单\")
if num == \"1\":
self.update_book()
def delete_book(self):
\"\"\"
[3]: 删除图书
:return:
\"\"\"
print(\"****************删除图书****************\")
book_id = input(\"请输入需要修改的图书ID：\")
result = db.query(\"select * from books where id={};\".format(book_id), one=True)
if result:
print(\"当前数据为：{}\".format(result))
confirm_num = input(\"确定需要删除这本书吗？确认请按1，取消请按2：\")
if confirm_num == \"1\":
db.update(\"delete from books where id={};\".format(book_id))
print(\"删除成功\")
else:
print(\"已确认不删除该书籍~\")
else:
print(\"系统中未找到该书籍！\")
num = input(\"继续删除请输入1， 回车退回主菜单\")
if num == \"1\":
self.delete_book()
def query_book(self):
\"\"\"
[4]: 查询图书
:return:
\"\"\"
print(\"****************查询图书****************\")
name = input(\"请输入您要查询的图书名称（模糊匹配）：\")
if name:
result = db.query(\"select * from books where name like \'%{}%\';\".format(name))
if result:
print(\"当前查询到如下书籍信息：{}\".format(result))
else:
print(\"未查询到相关书籍信息~\")
else:
print(\"书名不能为空！\")
num = input(\"继续查询请输入1， 回车退回主菜单\")
if num == \"1\":
self.query_book()
def book_list(self):
\"\"\"
[5]: 图书列表
:return:
\"\"\"
print(\"****************图书列表****************\")
result = db.query(\"select * from books;\")
for i in result:
print(\"编号：{}， 书籍名：{}， 位置：{}， 状态：{}， 借阅人：{}\".format(i[\"id\"], i[\"name\"], i[\"position\"], i[\"status\"],
i[\"borrower\"]))
def borrow_book(self):
\"\"\"
[6]: 出借图书
:return:
\"\"\"
print(\"****************出借图书****************\")
book_id = input(\"请输入需要借阅的图书ID：\")
result = db.query(\"select * from books where id={};\".format(book_id), one=True)
if result:
if result[\"status\"] == \"出借\":
print(\"抱歉，该书已经借出！\")
else:
while True:
borrower = input(\"请输入借阅者的名字：\")
if borrower:
db.update(\"update books set borrower=\'{}\' where id={};\".format(borrower, book_id))
db.update(\"update books set status=\'出借\' where id={};\".format(book_id))
print(\"图书借阅成功~\")
break
else:
print(\"借阅者的名字不能为空， 请重新输入\")
else:
print(\"未查询到相关书籍信息~\")
num = input(\"继续借阅请输入1， 回车退回主菜单\")
if num == \"1\":
self.borrow_book()
def back_book(self):
\"\"\"
[7]: 归还图书
:return:
\"\"\"
print(\"****************归还图书****************\")
book_id = input(\"请输入需要归还的图书ID：\")
result = db.query(\"select * from books where id={};\".format(book_id), one=True)
if result:
if result[\"status\"] == \"在库\":
print(\"该书是在库状态，请确认图书编号是否正确！\")
else:
db.update(\"update books set status=\'在库\' where id={};\".format(book_id))
db.update(\"update books set borrower=\'\' where id={};\".format(book_id))
print(\"书籍归还成功~\")
else:
print(\"未查询到相关书籍信息~\")
num = input(\"继续归还书籍请输入1， 回车退回主菜单\")
if num == \"1\":
self.borrow_book()
def quit(self):
\"\"\"
[8]: 退出
:return:
\"\"\"
print(\"****************退出****************\")
db.close()
def main(self):
\"\"\"
程序运行的流程控制
:return:
\"\"\"
print(\"---------------欢迎进入图书管理系统----------------\")
while True:
self.print_menu()
num = input(\"请输入选项：\")
if num == \"1\":
self.add_book()
elif num == \"2\":
self.update_book()
elif num == \"3\":
self.delete_book()
elif num == \"4\":
self.query_book()
elif num == \"5\":
self.book_list()
elif num == \"6\":
self.borrow_book()
elif num == \"7\":
self.back_book()
elif num == \"8\":
self.quit()
break
else:
print(\"您的输入有误~ 请按照菜单提示输入，谢谢！\")
if __name__ == \"__main__\":
book = BookManage()
book.main()
© 版权声明
THE END
暂无评论内容