【SRE】python+flask框架实现基本CRUD

背景：python练习

描述：python3、flask框架、centos7、基本的http请求、基本的sql增删改查。基本步骤参考flask官方文档，详情见代码注释，本文只做练习记录使用。

代码如下：

'''
Author: yaotengjian
Date: 2023-03-24 16:10:27
LastEditors: yaotengjian
LastEditMail: 
LastEditTime: 2023-03-29 22:38:20
FilePath: /py-flaskweb/flaskr/__init__.py
Description: creat_app 是一个应用工厂函数
#工厂模式 是一个很常用的用于创建对象的模式
#数据库实例的创建,工厂模式创建flask实例。 sqlite(不需要搭建另外的服务器)。
# 运行项目本地ip不能访问只能127.0.0.1访问,修改启动参数:flask run --host=0.0.0.0 --port=5000
#flask文档 http://djyqxbc.vip/static/web_page/flask1.1.1-%E4%B8%AD%E6%96%87%E7%89%88/index.html
#sqlite语法: https://www.runoob.com/sqlite/sqlite-insert.html
Copyright (c) 2023 by yaotengjian, All Rights Reserved.
'''
import os

from flask import Flask
from flask import request


def create_app(test_config=None):
    # create and configure the app
    app = Flask(__name__, instance_relative_config=True)
    app.config.from_mapping(
        SECRET_KEY='dev',
        DATABASE=os.path.join(app.instance_path, 'flaskr.sqlite'),
    )

    from . import db
    db.init_app(app)

    def query_db(query, args=(), one=False):
        cur = db.get_db().execute(query, args)
        rv = cur.fetchall()
        cur.close()
        return (rv[0] if rv else None) if one else rv

    if test_config is None:
        # load the instance config, if it exists, when not testing
        app.config.from_pyfile('config.py', silent=True)
    else:
        # load the test config if passed in
        app.config.from_mapping(test_config)

    # ensure the instance folder exists
    try:
        os.makedirs(app.instance_path)
    except OSError:
        pass

    # a simple page that says hello
    @app.route('/hello')
    def hello():
        return 'Hello, World!'

    @app.route('/')
    def hello_world():
        return 'Hello, World!'

    @app.route('/name', methods=['GET', 'POST'])
    def get_name():
        if request.method == 'POST':
            return 'tengjian from POST'
        else:
            return 'tengjian from GET'

    @app.route('/fans')
    def get_fans():
        return '100000'

    # 用户资料endpoint
    # R: Read 读取创建的user profile /GET
    # C: Create 创建一个user profile /POST
    # U: Update 更新创建的user profile /PUT
    # D: Delete 删除创建的user profile /DELETE
    @app.route('/userProfile', methods=["GET", "POST", "PUT", "DELETE"])
    def userProfile():
        if request.method == 'GET':
            # name = request.args.get('name', '')
            uid = request.args.get('uid', 1)
            # 3. 写sql
            query = "SELECT * FROM userProfile WHERE id = {}".format(uid)
            # 通过用户的id来查询用户资料
            result = query_db(query, one=True)
            # 1. 获取数据库连接
            # 2. 获取一个数据库的游标 cursor
            # 4. 执行sql
            # not robust at all !
            if result is None:
                return dict(message="user doesn't exist")
            else:
                username = result['username']
                fans = result['fans']
                print(result['username'])
                print(result['fans'])
                return dict(username=username, fans=fans)
            # 5. 处理从数据库里读取的数据
            # 6. 将数据返回给调用者
        elif request.method == 'POST':
            # name
            # fans
            print(request.json)
            name = request.json.get('name')
            fans = request.json.get('fans')
            # 获取post body中的name和fans
            # 插入新的数据到数据库
            # 1. 获取数据库连接
            connection = db.get_db()
            query = "INSERT INTO userProfile (username,fans) values('{}',{})".format(
                name, fans)
            print(query)
            #  curl -X POST http://ip:5000/userProfile  -H 'Content-Type: application/json' -d '{"name":"sunshangxiang","fans":123}'
            # 2. 执行
            try:
                cursor = connection.execute(query)
                # 3. DML data manipulate language 没关系
                # 当你对数据库的数据有改动的时候，需要commit，否则改动不会生效
                # execute的时候就回去数据库里执行这条sql，如果有错误，会报错
                connection.commit()
                print(cursor.lastrowid)
                # select * from userProfile where id =5
                return dict(success=True)
            except:
                return dict(success=False,
                            message="username exist",
                            errorCode=1)
        elif request.method == 'PUT':
            # update
            # curl -X PUT http://ip:5000/userProfile?uid=1  -H 'Content-Type: application/json' -d '{"name":"WANGBADAN","fans":131415}'
            name = request.json.get('name')
            fans = request.json.get('fans')
            uid = request.args.get('uid', 1)
            connection = db.get_db()
            query = "update userProfile set username='{}',fans={} where id={}".format(
                name, fans, uid)
            connection.execute(query)
            connection.commit()
            return dict(success=True)

        elif request.method == 'DELETE':
            # delete
            uid = request.args.get('uid', 1)
            connection = db.get_db()
            query = "delete from userProfile where id = {}".format(uid)
            connection.execute(query)
            connection.commit()
            return dict(success=True)

    return app


# python3 -m venv venv
# . venv/bin/activate
# export FLASK_APP=flaskr
# export FLASK_ENV=development
# export FLASK_DEBUG=1
# flask run --host=0.0.0.0 --port=5000

sql相关：

'''
Author: yaotengjian
Date: 2023-03-26 17:04:27
LastEditors: yaotengjian
LastEditMail: 
LastEditTime: 2023-03-29 16:17:27
FilePath: /py-flaskweb/flaskr/db.py
Description:

Copyright (c) 2023 by yaotengjian, All Rights Reserved.
'''
import sqlite3

import click
from flask import current_app, g
from flask.cli import with_appcontext


def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(current_app.config['DATABASE'],
                               detect_types=sqlite3.PARSE_DECLTYPES)
        g.db.row_factory = sqlite3.Row

    return g.db


def init_db():
    db = get_db()  #get_db 返回一个数据库连接，用于执行文件中的命令。

    with current_app.open_resource('schema.sql') as f:
        db.executescript(f.read().decode('utf8'))


def close_db(e=None):
    db = g.pop('db', None)

    if db is not None:
        db.close()


@click.command('init-db')
@with_appcontext
def init_db_command():
    """Clear the existing data and create new tables."""
    init_db()
    click.echo('Initialized the database.')


#close_db 和 init_db_command 函数需要在应用实例中注册，否则无法使用。


def init_app(app):
    app.teardown_appcontext(close_db)  #告诉 Flask 在返回响应后进行清理的时候调用此函数。
    app.cli.add_command(init_db_command)

--cat schema.sql
DROP TABLE IF EXISTS userProfile;
CREATE TABLE userProfile (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    fans INTEGER NOT NULL DEFAULT 0
);