question_answer

"# 数据库准备\n## 创建数据库\nCREATE DATABASE bms charset=utf8;\n\n## 切换数据库\nUSE bms;\n\n##\
\ 查看数据库中有哪些数据表\nshow tables;\n\n## 创建数据表（字段根据需求去创建）\nCREATE TABLE book (\n bid\
\ INT PRIMARY KEY AUTO_INCREMENT,\n name CHAR(50),\n price FLOAT,\n summary\
\ CHAR(50),\n quantity INT\n);\n\n## 插入数据\nINSERT INTO books (name, price, summary,\
\ quantity) \nVALUES(\"Python编程从入门到实践\", 69.8, \"python零基础入门自学教材程序设计软件开发书籍\", 2),\n\
(\"C++从入门到精通\", 37.6, \"C++零基础入门自学教材\", 4),\n(\"Java核心技术系列书籍共8册\", 671.0, \"开发基础高级特性（原书第12版）\"\
, 1),\n(\"JavaScript高级程序设计\", 68.4, \"编程语言与程序设计类书籍 JS从入门到精通\", 3);\n\n```python\n\
from flask import Flask, render_template, request, redirect\nimport pymysql\n\n\
# 创建 flask 实例\napp = Flask(__name__)\n\n# 连接数据库\ndb_connect = pymysql.Connect(\n\
\ host=\"mysql.hogwarts.ceshiren.com\",\n port=3306,\n user=\"stu\",\n\
\ password=\"hogwarts_stu\",\n database=\"hogwarts_stu\",\n charset=\"\
utf8\"\n)\n\n# 首页接口\n@app.route(\"/\")\ndef index():\n return render_template(\"\
index.html\")\n\n# 处理数据列表的接口\n@app.route(\"/list\")\ndef list_book():\n # 创建游标对象\n\
\ cursor = db_connect.cursor()\n # 编写SQL语句\n sql_str = '''select * from\
\ book '''\n # 执行SQL\n cursor.execute(sql_str)\n # 获取查询结果\n data = cursor.fetchall()\n\
\ # 关闭游标对象\n cursor.close()\n # 定义一个空列表，用来保存返回的数据\n # datas = []\n \
\ # for item in data:\n # book = {}\n # book[\"bid\"] = item[0]\n\
\ # book[\"name\"] = item[1]\n # book[\"price\"] = item[2]\n #\
\ book[\"summary\"] = item[3]\n # book[\"quantity\"] = item[4]\n #\
\ datas.append(book)\n datas = [{\"bid\": s[0], \"name\": s[1], \"price\"\
: s[2], \"summary\": s[3], \"quantity\": s[4]} for b in data]\n return datas\n\
\n\n# 添加数据接口\n@app.route(\"/add\", methods=[\"GET\", \"POST\"])\ndef add_book():\n\
\ if request.method == \"GET\":\n return render_template(\"add.html\"\
)\n else:\n name = request.values.get(\"name\")\n price = request.values.get(\"\
price\")\n summary = request.values.get(\"summary\")\n quantity =\
\ request.values.get(\"quantity\")\n cursor = db_connect.cursor()\n \
\ sql_str = f\"\"\" insert into book (name, price, summary, quantity) values('{name}','{price}','{summary}','{quantity}')\
\ \"\"\"\n cursor.execute(sql_str)\n db_connect.commit()\n \
\ cursor.close()\n return redirect(\"/\")\n\n\n# 修改数据接口\n@app.route(\"/change/<bid>\"\
, methods=[\"GET\", \"POST\"])\ndef change_book(bid):\n if request.method ==\
\ \"GET\":\n return render_template(\"change.html\")\n else:\n \
\ name = request.values.get(\"name\")\n price = request.values.get(\"price\"\
)\n summary = request.values.get(\"summary\")\n quantity = request.values.get(\"\
quantity\")\n cursor = db_connect.cursor()\n sql_str = ''' update\
\ book set name=%s, price=%s, summary=%s, quantity=%s where bid = %s'''\n \
\ cursor.execute(sql_str,[name, price, summary, quantity, bid])\n db_connect.commit()\n\
\ cursor.close()\n return redirect(\"/\")\n\n# 修改数据时，修改页面的回显数据接口\n\
@app.route(\"/changeData/<bid>\")\ndef change_data(bid):\n cursor = db_connect.cursor()\n\
\ sql_str = f''' select * from book where bid = {bid} '''\n cursor.execute(sql_str)\n\
\ item = cursor.fetchone()\n book = {}\n book[\"bid\"] = item[0]\n book[\"\
name\"] = item[1]\n book[\"price\"] = item[2]\n book[\"summary\"] = item[3]\n\
\ book[\"quantity\"] = item[4]\n cursor.close()\n return book\n\n# 删除接口\n\
@app.route(\"/delete/<bid>\")\ndef delete_book(bid):\n cursor = db_connect.cursor()\n\
\ sql_str = f''' delete from book where bid = {bid} '''\n cursor.execute(sql_str)\n\
\ db_connect.commit()\n cursor.close()\n return redirect(\"/\")\n\n\n#\
\ 搜索接口，要求可以在name和summary字段中进行模糊搜索\n@app.route(\"/search\")\ndef search_book():\n\
\ # 将用户提交过来的搜索内容取出来\n word = f\"%{request.values.get('name')}%\" # %CSS%\n\
\ cursor = db_connect.cursor()\n sql_str = ''' select * from book where name\
\ like %s or summary like %s'''\n cursor.execute(sql_str,[word, word])\n data\
\ = cursor.fetchall()\n # 关闭游标对象\n cursor.close()\n # 定义一个空列表，用来保存返回的数据\n\
\ # datas = []\n # for item in data:\n # book = {}\n # book[\"\
bid\"] = item[0]\n # book[\"name\"] = item[1]\n # book[\"price\"]\
\ = item[2]\n # book[\"summary\"] = item[3]\n # book[\"quantity\"\
] = item[4]\n # datas.append(book)\n datas = [{\"bid\": s[0], \"name\"\
: s[1], \"price\": s[2], \"summary\": s[3], \"quantity\": s[4]} for b in data]\n\
\ return datas\n\n\nif __name__ == '__main__':\n app.run(debug=True, port=5050)\n\
\n```\n"