图书管理系统
import pymysql
from flask import Flask, url_for, redirect, request
from flask import Blueprint
# 创建 Flask 应用程序实例
app = Flask(__name__)
#图书管理系统
# 数据库
class db_somthing():
def __init__(self):
self.__ip = '101.132.159.87'
self.__port = 3306
self.__users = 'petclinic'
self.__pwd = 'petclinic'
# 查询
def select_sql(self,sql):
db = pymysql.connect(
host= self.__ip,
user= self.__users,
password= self.__pwd,
port= self.__port,
charset="utf8")
# 创建一个游标对象 cursor
cursor = db.cursor()
try:
# 执行 SQL 语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
return results
except:
print("Error: unable to fetch data")
# 关闭数据库连接
db.close()
# 添加
def inster_sql(self,sql,values):
# 创建数据库连接
db = pymysql.connect(
host= self.__ip,
user= self.__users,
password= self.__pwd,
port= self.__port,
charset="utf8")
# 创建一个游标对象 cursor
cursor = db.cursor()
try:
# 执行 SQL 语句
cursor.execute(sql,values)
# cursor.execute(sql, list(data.values()))
# 提交更改到数据库
db.commit()
print("Record inserted successfully")
except:
# 如果出现错误则回滚更改
db.rollback()
print("Error: unable to insert record")
# 关闭数据库连接
db.close()
# 更新
def update_sql(self,sql):
# 创建连接对象
conn = pymysql.connect(
host= self.__ip,
user= self.__users,
password= self.__pwd,
port= self.__port,
charset="utf8")
# 创建游标对象
cursor = conn.cursor()
try:
# 执行SQL UPDATE语句
cursor.execute(sql)
# 提交更改
conn.commit()
print("UPDATE operation successful!")
except Exception as e:
# 发生错误时回滚更改
conn.rollback()
print("UPDATE operation failed:", e)
# 关闭连接和游标对象
cursor.close()
conn.close()
# 删除
def del_sql(self,sql):
# 建立数据库连接
conn = pymysql.connect(
host= self.__ip,
user= self.__users,
password= self.__pwd,
port= self.__port,
charset="utf8")
# 创建游标对象
cursor = conn.cursor()
# 执行DELETE语句
try:
cursor.execute(sql)
conn.commit()
except:
conn.rollback()
# 关闭游标和连接
cursor.close()
conn.close()
# 1. 蓝图的声明
goods_router = Blueprint(name="goods", import_name=__name__)
# 2. 路由定义
@goods_router.route("/",methods=["GET"])
def user_list():
sql = 'SELECT * FROM petclinic.weiqi_book'
rep = db.select_sql(sql)
print(rep)
print(rep)
# return str(rep).encode()
return {"code":0, "msg": "get success", "data": [rep]}
@goods_router.route("/add", methods=["POST"])
def add():
'''
{
"name" : "very",
"price" : 10.25,
"summary" : "good good",
"quantity" : 5000
}
'''
# 获取 JSON 格式请求体
data = request.json
sql = "INSERT INTO petclinic.weiqi_book ({0}) VALUES ({1})".format(
", ".join(data.keys()),
", ".join(["%s"] * len(data))
)
values = tuple(data.values())
db.inster_sql(sql, values)
print('添加成功')
return redirect(url_for("goods.user_list"))
@goods_router.route("/update", methods=["POST"])
def update():
'''
{
"name" : "very",
"price" : 10.25,
"summary" : "good good",
"quantity" : 5000
}
'''
data = request.json
print(data)
# 获取 JSON 格式请求体
sql = f"UPDATE petclinic.`weiqi_book` SET `price` = {data['price']}, summary = '{data['summary']}' WHERE `name` = '{data['name']}'"
# 使用参数执行更新语句
db.update_sql(sql)
return redirect(url_for("goods.user_list"))
@goods_router.route("/delete", methods=["DELETE"])
def delete():
'''
{
"bid" : 1
}
'''
data = request.json
sql = f"DELETE FROM petclinic.weiqi_book where bid={data['bid']}"
db.del_sql(sql)
return redirect(url_for("goods.user_list"))
# 定义动态路由
@goods_router.route("/search/<name>", methods=["GET"])
def search(name):
sql = f"SELECT * FROM petclinic.weiqi_book where name like '%{name}%'"
rep = db.select_sql(sql)
return {"code": 0, "msg": "get success", "data": [rep]}
# 运行应用程序
if __name__ == '__main__':
db = db_somthing()
'''
创建weiqi_book数据表
CREATE TABLE weiqi_book (
`bid` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR (255),
`price` DECIMAL (10, 2),
`summary` TEXT,
`quantity` INT
);
'''
# 注册蓝图
app.register_blueprint(goods_router)
app.run(port=5055, debug=True)