创建数据库
sqlalchemy数据类型
migrate修改
注意：sqlite不支持删除或更改列
虚拟环境安装包
mysql操作
迁移数据
过滤后将数据转化为json
def get_static_fileds(class_type):
return list(
k for k in class_type.__dict__.keys()
if not k.startswith("_") and
not callable(class_type.__dict__.get(k)))
def db_to_json(class_obj):
fildes = get_static_fileds(type(class_obj))
spoce = {"class_obj": class_obj}
return {k: eval(f"class_obj.{k}", spoce) for k in fildes}
func_info = db.session.query(FuncInfo).filter(FuncInfo.time == date).all()
func_info = [db_to_json(item) for item in func_info]
flask 操作数据库工具
# 装饰器
import functools
import json
from flask import request
from flask_cors import cross_origin
error_result = lambda error_info=None: {"state": "faild", "msg": error_info}
success_result = lambda result=None: {"state": "success", "result": result}
def get_static_fileds(class_type):
return list(
k for k in class_type.__dict__.keys()
if not k.startswith("_") and
not callable(class_type.__dict__.get(k)))
def db_to_json(class_obj):
fildes = get_static_fileds(type(class_obj))
spoce = {"class_obj": class_obj}
return {k: eval(f"class_obj.{k}", spoce) for k in fildes}
def add_db(db, db_type, uni_attr=None, only_attr: str = None):
"""
添加 数据库记录 装饰器
:param db_type:
:param uni_attr: id 自增键
:param only_attr: 只允许有一个的键
:return:
"""
def dec(func):
@functools.wraps(func)
def wrap():
result = func()
if result:
return result
add_dict = {k: v for k, v in request.args.items()}
fileds = get_static_fileds(db_type)
for filed in fileds:
if filed not in add_dict and filed != uni_attr:
return error_result(f"缺少参数{filed}")
if only_attr:
only_attrs = only_attr.split("|")
filter_only = list(i for i in only_attrs if i not in fileds)
if filter_only:
return error_result(f"服务器设置错误")
scope = {"db_type": db_type, "add_dict": add_dict, "db": db}
args = ','.join(f"db_type.{attr}==add_dict.get('{attr}')"
for attr in fileds if attr in only_attrs)
res = eval(
f"db.session.query(db_type).filter({args})", scope).all()
if len(res) > 0:
return error_result(f"该属性不允许重复添加:{only_attr}")
param_dict = {k: add_dict.get(k) for k in fileds}
db.session.add(db_type(**param_dict))
db.session.commit()
return success_result()
return wrap
return dec
def get_db(db, db_type):
"""
获取 数据库记录 装饰器
:param db_type:
:param uni_attr: id 自增键
:param only_attr: 只允许有一个的键
:return:
"""
def dec(func):
@functools.wraps(func)
def wrap():
result = func()
if result:
return result
get_dict = {k: v for k, v in request.args.items()}
fileds = get_static_fileds(db_type)
if get_dict:
scope = {"db_type": db_type, "get_dict": get_dict, "db": db}
args = ','.join(f"db_type.{attr}==get_dict.get('{attr}')"
for attr in fileds if attr in get_dict)
res = eval(
f"db.session.query(db_type).filter({args})", scope).all()
else:
res = db.session.query(db_type).all()
res = [db_to_json(item) for item in res]
return success_result(res[::-1])
return wrap
return dec
def delete_db(db, db_type):
"""
删除 数据库记录 装饰器
:param db_type:
:param uni_attr: id 自增键
:param only_attr: 只允许有一个的键
:return:
"""
def dec(func):
@functools.wraps(func)
def wrap():
result = func()
if result:
return result
fileds = get_static_fileds(db_type)
get_dict = {k: v for k, v in request.args.items() if k in fileds}
if get_dict:
scope = {"db_type": db_type, "get_dict": get_dict, "db": db}
args = ','.join(f"db_type.{attr}==get_dict.get('{attr}')"
for attr in fileds if attr in get_dict)
eval(
f"db.session.query(db_type).filter({args})", scope
).delete()
db.session.commit()
else:
return error_result("缺少参数")
return success_result("删除成功")
return wrap
return dec
def modify_db(db, db_type):
def dec(func):
@functools.wraps(func)
def wrap():
result = func()
if result:
return result
fileds = get_static_fileds(db_type)
datas = json.loads(request.get_data().decode('utf-8'))
get_dict = {k: v for k, v in datas.get("old").items() if k in fileds}
new_dict = {k: v for k, v in datas.get("new").items() if k in fileds}
if get_dict and new_dict:
scope = {"db_type": db_type, "get_dict": get_dict, "db": db}
args = ','.join(f"db_type.{attr}==get_dict.get('{attr}')"
for attr in fileds if attr in get_dict)
res = eval(
f"db.session.query(db_type).filter({args})", scope
).all()
for item in res:
for key, values in new_dict.items():
setattr(item, key, values)
db.session.commit()
else:
return error_result("缺少参数")
return success_result("修改成功")
return wrap
return dec
# 装饰器
# 创建crud方法
def get_empty_func(func_name):
func_dict = {}
exec(f"""
def {func_name}():
pass
""", func_dict)
return func_dict.get(func_name)
def create_crud(app, db, db_type, base_name, add_param_dict=None):
if not add_param_dict:
add_param_dict = {}
base_func_name = base_name.replace('/', '_')
app.route(f"/add/{base_name}")(
cross_origin()(
add_db(db, db_type, **add_param_dict)(
get_empty_func(f"add_{base_func_name}"))))
app.route(f"/get/{base_name}")(
cross_origin()(
get_db(db, db_type)(
get_empty_func(f"get_{base_func_name}"))))
app.route(f"/delete/{base_name}")(
cross_origin()(
delete_db(db, db_type)(
get_empty_func(f"delete_{base_func_name}"))))
app.route(f"/modify/{base_name}", methods=["POST"])(
cross_origin()(
modify_db(db, db_type)(
get_empty_func(f"modify_{base_func_name}"))))
