業(yè)務(wù)邏輯不能用http狀態(tài)碼判斷，應(yīng)該有自己的邏輯判斷。想要前端需要判斷（好多if…else），所以需要標(biāo)準(zhǔn)化，標(biāo)準(zhǔn)化返回。
json標(biāo)準(zhǔn)化返回:
最外面：data,message,code三個(gè)字段。
data：返回的數(shù)據(jù)
code：應(yīng)用狀態(tài)碼：先設(shè)計(jì)好，成功-0，失敗–登錄失敗1，注冊(cè)失敗2
msg：返回的說明
我們寫的接口也要按照這個(gè)格式來

添加libs/response.py

def generate_response(data = None, msg = "success!", code = 10000):# 約定返回的數(shù)據(jù)格式if data is None:data = []return {"code": code,"msg": msg,"data": data}

然后修改返回：
login.py

from flask import Blueprint, request
from config.settings import user_dict
from libs.response import generate_responselogin_bp = Blueprint("login_bp", __name__, url_prefix="/v1")@login_bp.route("login")
def login():user = request.json.get("username")passwd = request.json.get("passwd")local_user_passwd = user_dict.get(user)if local_user_passwd and passwd == local_user_passwd:return generate_response(msg="success")return generate_response(msg="login fail!", code=10001)

register.py

from flask import Blueprint, request
from config.settings import user_dict
from libs.response import generate_responseregister_bp = Blueprint("register_bp", __name__, url_prefix="/v1")@register_bp.route("register")
def register():username = request.json.get("username")passwd = request.json.get("passwd")re_passwd = request.json.get("re_passwd")if not (username and passwd and re_passwd):return generate_response(msg="參數(shù)傳遞不完整", code=3)elif passwd != re_passwd:return generate_response(msg="注冊(cè)密碼不一致", code=2)elif username in user_dict:return generate_response(msg="用戶已注冊(cè)",code=1)else:user_dict[username] = passwdprint(f"user_dict is {user_dict}")return generate_response(msg="register success!", code=10000)

連接數(shù)據(jù)庫（為了避免頻繁的打開關(guān)閉消耗過多資源）

libs/conn_mysql.py

import pymysql
from config.settings import DB_PASS, DB_PORT, DB_SCHEM, DB_USER, DB_HOSTdef conn_mysql():conn = pymysql.connect(host = DB_HOST,port = DB_PORT,user = DB_USER,password = DB_PASS,db = DB_SCHEM)return conn

為了只連一次，綁到app上
app.py添加：
上面返回了一個(gè)連接對(duì)象conn，把他作為一個(gè)屬性交給了sq_app對(duì)象，再給sq_app對(duì)象隨意的可以設(shè)置屬性，自己定義（mysql_db）。所以把連接交給了app。

def create_app():#連接數(shù)據(jù)庫sq_app.mysql_db = conn_mysql()

剛好flask提供了一個(gè)current_app，在你請(qǐng)求過來的時(shí)候，會(huì)把你當(dāng)前的app的上下文內(nèi)容放在current_app里。
router/product_view/product.py

from . import product_bp
from flask import current_app
from libs.response import generate_response@product_bp.route("/product/get")
def get_product():# import pymysql# db = pymysql.connect(host='192.168.1.150',#                      user='jiangda97',#                      password='Jiangda123#',#                      database='sq-flask')cursor = current_app.mysql_db.cursor()cursor.execute("select * from product_info")data = cursor.fetchall()print(data)# db.close()if data:return generate_response(data=data, msg="get product info success!")else:return generate_response(msg="get data empty", code = 4)

router/product_view/__init__.py

from flask import Blueprint
product_bp = Blueprint("product_bp", __name__, url_prefix="/v1")from . import product

分表
優(yōu)點(diǎn)：節(jié)省空間，避免數(shù)據(jù)不必要的膨脹。
缺點(diǎn):

新增了一個(gè)product_kind_table表

select product_info.product_id, product_info.product_name, product_kind_table.kind, product_info.product_price, product_info.product_address
from product_info inner join product_kind_table
on product_kind = id
where product_id = 1

修改代碼router/product_view/product.py

    # 通過url攜帶參數(shù)來傳遞idid = request.args.get("id")if id is None:sql_str = f"select product_info.product_id, product_info.product_name, product_kind_table.kind, product_info.product_price, product_info.product_address \from product_info inner join product_kind_table \on product_kind = id\where product_id = {id}"else:sql_str = f"select product_info.product_id, product_info.product_name, product_kind_table.kind, product_info.product_price, product_info.product_address \from product_info inner join product_kind_table \on product_kind = id\where product_id = {id}"cursor = current_app.mysql_db.cursor()cursor.execute(sql_str)data = cursor.fetchall()# print(data)# db.close ()if data:return generate_response(data=data, msg="get product info success!")else:return generate_response(msg="get data empty", code = 4)

ORM

object relation mapping對(duì)象關(guān)系映射

orm對(duì)象持久化對(duì)象

數(shù)據(jù)庫的表 – 類

表中的字段 – 屬性

一行行記錄 – 對(duì)象

models/__init__.py

from flask_sqlalchemy import SQLAlchemy#生成對(duì)象映射實(shí)例（db就是我們的中間層）
db = SQLAlchemy()def init_app_db(app):db.init_app(app)

models/product.py

from . import dbclass ProductInfo(db.Model):__tablename__ = "product_info"product_id = db.Column(db.Integer, primary_key=True, autoincrement=True)product_name = db.Column(db.String(256))product_kind = db.Column(db.Integer)product_price = db.Column(db.Float)product_address = db.Column(db.String(128))

都得運(yùn)行：init文件添加from . import product

綁定到核心對(duì)象：app.py文件添加

    import modelsmodels.init_app_db(sq_app)

最后運(yùn)行報(bào)錯(cuò)：

RuntimeError: Either ‘SQLALCHEMY_DATABASE_URI’ or ‘SQLALCHEMY_BINDS’
must be set.

意思是需要設(shè)置這兩個(gè)變量，即orm映射的數(shù)據(jù)庫信息。

config/settings.py添加

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://jiangda97:Jiangda123#@192.168.1.150:3306/sq-flask"

之前我們?cè)赼pp.py將settings都讀入sq_app.config里了，且是都大寫的key。
剛好我們的SQLAchemy底層就是會(huì)自動(dòng)讀取sq_app.config里的關(guān)于連接數(shù)據(jù)庫的操作。

SQLALCHEMY_DATABASE_URI = “mysql+pymysql://jiangda97:Jiangda123#@192.168.1.150:3306/sq-flask”
底層+用的連接方式://用戶名:密碼@host:port/數(shù)據(jù)庫名

然后我們準(zhǔn)備用它來完成一個(gè)增加操作，在router/product_view/product.py

from models.product import ProductInfo
from models import db# 新增數(shù)據(jù)庫記錄
@product_bp.route("/product/add", methods=['POST'])
def product_add():# 接收客戶端的傳遞pro_name = request.json.get("proname")pro_kind = request.json.get("prokind")pro_price = request.json.get("proprice")pro_address = request.json.get("proadd")# 實(shí)例化類成對(duì)象proinfo = ProductInfo()# 設(shè)置屬性proinfo.product_name = pro_nameproinfo.product_kind = pro_kindproinfo.product_price = pro_priceproinfo.product_address = pro_address# 實(shí)例化并設(shè)置屬性也可以這么寫# proinfo = ProductInfo(product_name = pro_name,#                       product_kind = pro_kind,#                       product_price = pro_price,#                       product_address = pro_address)# 生效到數(shù)據(jù)庫db.session.add(proinfo)db.session.commit()return generate_response(msg="add success!")

migrate

添加models/product.py
在該類下

    add_time = db.Column(db.DateTime, default=datetime.datetime.now())

數(shù)據(jù)庫遷移工具，版本管理 – flask-migrate

改server.py

# 數(shù)據(jù)庫遷移工具，版本管理 -- flask-migrate
from flask_migrate import Migrate
from models import dbmigrate = Migrate(sq_app, db)if __name__ == '__main__':sq_app.run(host = sq_app.config['HOST'],port = sq_app.config['PORT'],debug = sq_app.config['DEBUG'])

方便開發(fā)，不改變應(yīng)用邏輯，只是方便我們把orm映射的類，這個(gè)添加的字段生效到數(shù)據(jù)庫，不需要自己修改數(shù)據(jù)庫了。

terminal中輸入該命令，（在命令行操控flask – flask cli）

(venv) D:\sq-flask>flask --app server:sq_app db init
Creating directory 'D:\\sq-flask\\migrations' ...  done
Creating directory 'D:\\sq-flask\\migrations\\versions' ...  done
Generating D:\sq-flask\migrations\alembic.ini ...  done
Generating D:\sq-flask\migrations\env.py ...  done
Generating D:\sq-flask\migrations\README ...  done
Generating D:\sq-flask\migrations\script.py.mako ...  done
Please edit configuration/connection/logging settings in 'D:\\sq-flask\\migrations\\alembic.ini' befor
e proceeding.

然后就會(huì)產(chǎn)生一個(gè)migrations的文件夾

migrate單獨(dú)用不了，借助flask cli命令行工具，migrate綁定好app后，自動(dòng)創(chuàng)建好db命令。
初始化flask --app server:sq_app db init

–app 指定運(yùn)行哪個(gè)app
初始化會(huì)創(chuàng)建migrations的文件夾

可以隨時(shí)刪，再init，做了修改，提交版本！

(venv) D:\sq-flask>flask --app server:sq_app db migrate -m "add time"
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected removed table 'product_kind_table'
INFO  [alembic.autogenerate.compare] Detected added column 'product_info.add_time'
INFO  [alembic.autogenerate.compare] Detected NULL on column 'product_info.product_name'
INFO  [alembic.autogenerate.compare] Detected NULL on column 'product_info.product_kind'
INFO  [alembic.autogenerate.compare] Detected NULL on column 'product_info.product_price'
INFO  [alembic.autogenerate.compare] Detected NULL on column 'product_info.product_address'
Generating D:\衡山\2023-文老師\sq-flask\migrations\versions\43aac3b3bb51_add_time.py ...  done

upgrade就可以生效了

(venv) D:\sq-flask>flask --app server:sq_app db upgrade
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 43aac3b3bb51, add time

嚴(yán)格按照orm定義好的模型，保持?jǐn)?shù)據(jù)庫和模型一致，如果數(shù)據(jù)庫有，orm定義的模型沒有，則會(huì)把數(shù)據(jù)庫多出來的刪掉。

回退:flask --app server:sq_app db downgrade

命令行進(jìn)入上下文環(huán)境：(用來測(cè)試調(diào)試代碼)
flask --app server:sq_app shell

(venv) D:\sq-flask>flask --app server:sq_app shell
Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec  7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32
App: app
Instance: D:\sq-flask\instance
>>> from models.product import ProductInfo
>>> p1 = ProductInfo()
>>> p1.product_name = "3333"
>>> p1.product_kind =  2
>>> p1.product_price = 22
>>> p1.product_address = "山東"
>>> from models import db
>>> db.session.add(p1)
>>> db.session.commit()
>>>

查詢和修改：
修改其屬性。

>>> p2 = ProductInfo.query.get(3)
>>> p2
<ProductInfo 3>
>>> dir(p2)
['__abstract__', '__annotations__', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__for
mat__', '__fsa__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__',
'__lt__', '__mapper__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__
', '__sizeof__', '__str__', '__subclasshook__', '__table__', '__tablename__', '__weakref__', '_sa_class_manager',
'_sa_instance_state', '_sa_registry', 'add_time', 'metadata', 'product_address', 'product_id', 'product_kind', 'pr
oduct_name', 'product_price', 'query', 'query_class', 'registry']
>>> p2.product_name
'牛肉'
>>> p2.product_name = "牛肌肉"
>>> db.session.add(p2)
>>> db.session.commit()
>>>

刪除：

>>> p3 = ProductInfo.query.get(4)
>>> db.session.delete(p3)
>>> db.session.commit()

綜合：id通過url攜帶參數(shù)傳遞，完成修改和刪除
刪除：/product/modify – PUT
刪除：/product/delete – DELETE

router/product_view/product.py

@product_bp.route("/product/modify", methods=['PUT'])
def product_modify():# 接收客戶端的傳遞攜帶的參數(shù)id = request.args.get("id")p1 = ProductInfo.query.get(id)if p1:# 接收客戶端的傳遞pro_name = request.json.get("proname")pro_kind = request.json.get("prokind")pro_price = request.json.get("proprice")pro_address = request.json.get("proadd")p1.product_name = pro_namep1.product_kind = pro_kindp1.product_price = pro_pricep1.product_address = pro_addressdb.session.add(p1)db.session.commit()return generate_response(msg="modify success!")else:return generate_response(msg="no such product!", code=5)

修改：盡管你修改一個(gè)，但你提交的時(shí)候得提交全部的字段

刪除：

@product_bp.route("/product/delete", methods=['DELETE'])
def product_delete():id = request.args.get("id")p2 = ProductInfo.query.get(id)if p2:db.session.delete(p2)db.session.commit()return generate_response(msg="delete success")else:return generate_response(msg="no such product", code=6)

查詢：
query.get() 一般用來查詢主鍵
query.all() 查詢所有（列表類型）

>>> ProductInfo.query.filter_by(product_kind=1).all()
[<ProductInfo 1>, <ProductInfo 2>]>>> ProductInfo.query.filter(ProductInfo.product_kind == 1).all()
[<ProductInfo 1>, <ProductInfo 2>]