【自学开发之旅】Flask-标准化返回-连接数据库-分表-orm-migrate-增删改查（三）

业务逻辑不能用http状态码判断，应该有自己的逻辑判断。想要前端需要判断（好多if…else），所以需要标准化，标准化返回。
json标准化返回:
最外面：data,message,code三个字段。
data：返回的数据
code：应用状态码：先设计好，成功-0，失败–登录失败1，注册失败2
msg：返回的说明
我们写的接口也要按照这个格式来

添加libs/response.py

def generate_response(data = None, msg = "success!", code = 10000):# 约定返回的数据格式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="参数传递不完整", code=3)elif passwd != re_passwd:return generate_response(msg="注册密码不一致", code=2)elif username in user_dict:return generate_response(msg="用户已注册",code=1)else:user_dict[username] = passwdprint(f"user_dict is {user_dict}")return generate_response(msg="register success!", code=10000)

连接数据库（为了避免频繁的打开关闭消耗过多资源）

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添加：
上面返回了一个连接对象conn，把他作为一个属性交给了sq_app对象，再给sq_app对象随意的可以设置属性，自己定义（mysql_db）。所以把连接交给了app。

def create_app():#连接数据库sq_app.mysql_db = conn_mysql()

刚好flask提供了一个current_app，在你请求过来的时候，会把你当前的app的上下文内容放在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

分表
优点：节省空间，避免数据不必要的膨胀。
缺点:

新增了一个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携带参数来传递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对象关系映射

orm对象持久化对象

数据库的表 – 类

表中的字段 – 属性

一行行记录 – 对象

models/__init__.py

from flask_sqlalchemy import SQLAlchemy#生成对象映射实例（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))

都得运行：init文件添加from . import product

绑定到核心对象：app.py文件添加

    import modelsmodels.init_app_db(sq_app)

最后运行报错：

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

意思是需要设置这两个变量，即orm映射的数据库信息。

config/settings.py添加

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

之前我们在app.py将settings都读入sq_app.config里了，且是都大写的key。
刚好我们的SQLAchemy底层就是会自动读取sq_app.config里的关于连接数据库的操作。

SQLALCHEMY_DATABASE_URI = “mysql+pymysql://jiangda97:Jiangda123#@192.168.1.150:3306/sq-flask”
底层+用的连接方式://用户名:密码@host:port/数据库名

然后我们准备用它来完成一个增加操作，在router/product_view/product.py

from models.product import ProductInfo
from models import db# 新增数据库记录
@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")# 实例化类成对象proinfo = ProductInfo()# 设置属性proinfo.product_name = pro_nameproinfo.product_kind = pro_kindproinfo.product_price = pro_priceproinfo.product_address = pro_address# 实例化并设置属性也可以这么写# proinfo = ProductInfo(product_name = pro_name,#                       product_kind = pro_kind,#                       product_price = pro_price,#                       product_address = pro_address)# 生效到数据库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())

数据库迁移工具，版本管理 – flask-migrate

改server.py

# 数据库迁移工具，版本管理 -- 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'])

方便开发，不改变应用逻辑，只是方便我们把orm映射的类，这个添加的字段生效到数据库，不需要自己修改数据库了。

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.

然后就会产生一个migrations的文件夹

migrate单独用不了，借助flask cli命令行工具，migrate绑定好app后，自动创建好db命令。
初始化flask --app server:sq_app db init

–app 指定运行哪个app
初始化会创建migrations的文件夹

可以随时删，再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

严格按照orm定义好的模型，保持数据库和模型一致，如果数据库有，orm定义的模型没有，则会把数据库多出来的删掉。

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

命令行进入上下文环境：(用来测试调试代码)
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携带参数传递，完成修改和删除
删除：/product/modify – PUT
删除：/product/delete – DELETE

router/product_view/product.py

@product_bp.route("/product/modify", methods=['PUT'])
def product_modify():# 接收客户端的传递携带的参数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)

修改：尽管你修改一个，但你提交的时候得提交全部的字段

删除：

@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>]