简单的flask增删改查api接口编写

简单的flask增删改查api接口编写

编写好的架构图如下：
BookSim/
├── app.py
├── config.py
├── manager.py
├── MySQL.py
├── restapi
│?? └── testdb.py
├── static
│?? └── layui
│?? ├── css
│?? │?? ├── layui.css
│?? │?? └── modules
│?? │?? ├── code.css
│?? │?? ├── laydate
│?? │?? │?? └── default
│?? │?? │?? └── laydate.css
│?? │?? └── layer
│?? │?? └── default
│?? │?? ├── icon-ext.png
│?? │?? ├── icon.png
│?? │?? ├── layer.css
│?? │?? ├── loading-0.gif
│?? │?? ├── loading-1.gif
│?? │?? └── loading-2.gif
│?? ├── font
│?? │?? ├── iconfont.eot
│?? │?? ├── iconfont.svg
│?? │?? ├── iconfont.ttf
│?? │?? ├── iconfont.woff
│?? │?? └── iconfont.woff2
│?? └── layui.js
└── templates
├── author_book.html
└── bookdetail.html
文件说明：
config.py文件相当于settings.py文件里面记录了此次需要的数据库，redis。。的配置信息
app.py为一个flask的主入口对象
manager.py为一个封装的管理文件，可以通过Manager来进行ip和端口配置，和django的启动命令大体一致
通过查看源码Flask类默认的模板文件和静态文件目录如下
def init(
self,
import_name,
static_url_path=None,
static_folder=“static”,
static_host=None,
host_matching=False,
subdomain_matching=False,
template_folder=“templates”,
instance_path=None,
instance_relative_config=False,
root_path=None,
):
一个为templates目录，一个为static目录，都在根目录下创建
本人特别不喜欢django和flask的数据库查询模块，本次都是通过写一个mysql类来实现数据库的增删改查命令，看代码清晰明了，不用在进行主键外键关联和一对一一对多查询，非常不擅长这个。不过如果对orm使用牛逼的大佬可以直接使用orm，毕竟这个还是很简单明白，官方推荐的都不会差。下面进入正题，这次项目的实现目标是实现一个图书页面的增删改查，编辑页面由于本人前端比较low，代码编写能力弱就用了layui的ifrme窗口，为了网页尽快打开，直接下载了layui文件到本地，jquery采用了cdn在线方式，当然生产环境强烈要求必须放在本地
app.py文件内容：

from flask import  Flask,render_template,request,flash,redirect,jsonify
from MySQL import MysqlHelper
from restapi.testdb import  book
app=Flask(__name__)
app.register_blueprint(book,url_prefix='/books')
app.config.from_pyfile('config.py')
HOST=app.config.get('HOST')
PORT=app.config.get('PORT')
USERNAME=app.config.get('USERNAME')
PASSWORD=app.config.get('PASSWORD')
DATABASE=app.config.get('DATABASE')
mydb=MysqlHelper(HOST,USERNAME,PASSWORD,DATABASE,PORT)
@app.route("/index")
def index():
  mydb.connect()
  sql = "select a.id,a.name from author a"
  res = mydb.get_all(sql)
  lbook = []
  for i in res:
      sql = "select id,name,price from book where author=%d" % (i[0])
      bname = mydb.get_all(sql)
      print(bname)
      dic = {'authorname': i[1], 'bookinfo': [j for j in bname]}
      lbook.append(dic)
  print(lbook)
  try:
      mydb.close()
  except Exception as e:
      pass
  return render_template('author_book.html',res=lbook)

@app.route('/bookadd',methods=['post'])
def create_book():
  if request.method=='POST':
      authorname=request.form.get('authorname')
      bookname=request.form.get('bookname')
      bookprice=request.form.get('bookprice')
      sql='select count(1) from author a,book b where a.id=b.author and a.name="%s" and b.name="%s"'%(authorname,bookname)
      res=mydb.get_all(sql)
      if int(res[0][0])>0:
          flash("作者图书已经存在")
      else:
          sql='select count(1) from author where name="%s"'%(authorname)
          auex=mydb.get_all(sql)[0][0]
          if int(auex) == 0:
              insql='insert into author(name) values("{0}")'.format(authorname)
              mydb.insert(insql)
          else:
              pass
          sql='select id from author where name="{}"'.format(authorname);
          auid=mydb.get_all(sql)[0][0]
          sql='insert into book(name,author,price) values("{}",{},{})'.format(bookname,auid,bookprice)
          mydb.insert(sql)
          flash("图书已添加成功")
      return redirect('/index')
@app.route('/bookupd/<int:id>',methods=['POST','PUT'])
def update_book(id):
  price=request.form.get('price')
  sql='update book set price=%s where id=%s'%(price,id)
  print(sql)
  try:
      res=mydb.update(sql)
      return jsonify({'res':'数据更新成功'})
  except Exception as e:
      return  jsonify({'res':e})

@app.route('/bookdel/<int:id>',methods=['DELETE'])
def delete_book(id):
  print(id)
  sql='delete from  book where id=%s'%(id)
  print(sql)
  res=mydb.delete(sql)
  return jsonify({'res':res})


@app.route('/bookdetail/<int:id>',methods=['POST','GET'])
def get_detail(id):
  sql='select a.id,b.name,a.name,a.price from book a,author b where a.author=b.id and a.id=%s'%(id)
  res=mydb.get_all(sql)[0]
  print(res)
  return render_template('bookdetail.html',res=res)



          



if __name__=='__main__':
  app.run(host='192.168.56.104',debug=True)

config.py文件：

import os

SECRET_KEY = os.urandom(24)
DIALECT = 'mysql'
DRIVER = 'pymysql'
USERNAME = 'dbadmin'   # 改为自己的数据库用户名
PASSWORD = 'dbadmin'   # 改为自己的数据库密码
HOST = '192.168.56.104'
PORT = 3306
DATABASE = 'booksim'   # 改为自己新建的schema名
JSONIFY_MIMETYPE="application/json;charset=utf-8"
JSON_AS_ASCII=False
SQLALCHEMY_DATABASE_URI = "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALECT, DRIVER, USERNAME, PASSWORD, HOST, PORT, DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS = False

注意上面的JSON_AS_ASCII这个内容，主要为了后续的json在网页上中文不乱码
manager.py

from flask_script import  Manager
from app import app
manager=Manager(app)

if __name__=='__main__':
    manager.run()

MySQL.py文件

import pymysql
import  re

class MysqlHelper(object):
    conn = None

    def __init__(self, host, username, password, db=None,port=3306,unix_socket=None):
        self.host = host
        self.username = username
        self.password = password
        self.db = db
        self.port = port
        self.unix_socket=unix_socket

    def connect(self):
        if self.unix_socket:
            self.conn = pymysql.connect(host=self.host, port=self.port, user=self.username, password=self.password,unix_socket='/storage/mysql3307/mysql3307.sock')
            self.cursor = self.conn.cursor()
            print("hello world")
        else:
            self.conn = pymysql.connect(host=self.host, port=self.port, user=self.username, password=self.password, db=self.db,
                      )
            self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_one(self, sql, params=()):
        result = None
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return result

    def get_all(self, sql, params=()):
        list_data = ()
        try:
            self.connect()
            self.cursor.execute(sql, params)
            list_data = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return list_data

    def insert(self, sql, params=()):
        return self.__edit(sql, params)

    def update(self, sql, params=()):
        return self.__edit(sql, params)

    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self, sql, params):
        count = 0
        try:
            self.connect()
            count = self.cursor.execute(sql, params)
            self.conn.commit()
            self.close()
        except Exception as e:
            return e
        return count


if __name__=='__main__':
    sql='insert into author(name) values("小明")'
    mydb=MysqlHelper('192.168.56.104','dbadmin','dbadmin','booksim')
    mydb.insert(sql)

上面就是一个MySQL的数据库接口类，帮助我们实现数据库的增删改查
接下来我们写html文件，html文件正常情况下都是放在templates目录下，当然大佬可以随心所欲为所欲为，俺不是大佬就按照这个套路来
author_book.html文件内容

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="{{url_for('static',filename='layui/layui.js')}}"></script>
 <script src="{{url_for('static',filename='layui/css/layui.css')}}"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
</head>
<body>
<form action="/bookadd" method="POST">
    图书作者：<input type="text" name="authorname"><br>
    图书名称：<input type="text" name="bookname"><br>
    图书价格：<input type="text" name="bookprice"><br>
    <input type="submit" value="添加">&nbsp;&nbsp;<input type="reset" value="重置">
    {% for message in get_flashed_messages() %}
    <div class=flash>{{ message }}</div>
    {% endfor %}
</form>
<ul>
    {% for ressig in res %}
    <li>作者:{{ressig.authorname}}</li>
    <ul>{% for book in ressig.bookinfo %}
    <li>图书名称:{{ book[1] }}--图书价格:{{ book[2] }}--
        <button onclick="delbook({{book[0]}})">删除图书</button>--
        <button onclick="upbook({{book[0]}})">编辑图书</button>
  </li>
        {% endfor %}
        </ul>
    {% endfor %}
</ul>
<script>
function delbook(id) {
    $.ajax({
        url:'/bookdel/'+id,
        type:'DELETE',
        async:false,
        success:function (res) {
            console.log("成功删除")
             window.location.reload()
        },
        error:function (res) {
            console.log("删除失败"+res)

        }
        }

    )
}
function upbook(id) {

    layer.open({
        type: 2,
        content: '/bookdetail/'+id,
        end:function () {
            window.location.reload()
        }
    })

}
</script>
</body>
</html>

bookdetail.html内容

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form action="/bookupd/{{res[0]}}" method="POST">
    主键ID:<input type="text" readonly value="{{res[0]}}"><br>
    作者：<input type="text" value="{{res[1]}}" readonly>
    书名:<input type="text" value="{{res[2]}}" readonly><br>
    价格:<input type="text" name="price"  value="{{res[3]}}"><br>
    <input type="submit" value="提交">
</form>
</body>
</html>

代码描述完毕，上面没有采用蓝图，因为只是一个演示项目，就没有整那么大，直接很果断的一个app全部干完
开始跑代码，本人在linux服务器上安装了python3.8 virtual env环境
(python38) [root@mysql04 BookSim]# python manager.py runserver -h 192.168.56.104

• Serving Flask app “app” (lazy loading)
• Environment: production
  WARNING: This is a development server. Do not use it in a production deployment.
  Use a production WSGI server instead.
• Debug mode: off
• Running on http://192.168.56.104:5000/ (Press CTRL+C to quit)
  

直接进入index目录可以看到这个链接，数据是我提前放好的，表结构如下

点击编辑按钮会出来一个iframe弹窗

弹窗中的内容已经自动从数据库中取值并赋值了，除了价格这一栏剩下的均为不可改
修改之后点击提交如果成功了会有这个信息，做的很low大佬可以搞好看，点击关闭之后页面会自动刷新，window.location.reload()函数实现

form表单目前只支持get,post请求，put,delete这类请求不支持也没办法
一个简单的项目就这样完成了