在flask框架下,尽管也同样有flask-migrate,可将model映射到数据库中,但是在实际工程实践中,往往是先通过PowerDesigner、PDManer等软件进行数据库表的结构设计,此时可以通过此类软件直接导出数据库的建表语句,从而创建数据库表。
另外,我们在开发实践中发现,每一个model都需要增删改查操作,通常我们使用flask-sqlalchemy模块来作为ORM以避免原始SQL语句的编写,并将ORM封装成通用的函数供上层功能进行调用。而此类增删改查的函数,可以规整合并分别形成增、删、改、查4个基本的通用函数,可能在性能上略有下降,但免去了大量的定制化函数。
因此,需要一套机制从数据库表反向映射生成model,并生成最基本的增删改查语句,以简化大量的增删改查语句的Ctrl C/Ctrl V操作。
SQL到model
在flask中,有一个flask-sqlacodegen
包可以提供该功能
安装
pip install flask-sqlacodegen
使用
flask-sqlacodegen "mysql://root:pwd@127.0.0.1/food_db?charset=utf8" --outfile "common/models/User.py" --flask
model到ORM
个人编写了一个脚本,用于批量生成ORM函数,每一个model最终对应一个ORM类。
以如下的一个model为例
from ..globals.db import db
class TaskStatus(db.Model):
__tablename__ = 't_task_status'
id = db.Column(db.BigInteger, primary_key=True, info='主键')
req_time = db.Column(db.String(50), info='请求的时间戳')
req_source = db.Column(db.String(50), info='请求来源,页面/EXCEL/API')
req_user = db.Column(db.String(50), info='请求用户')
task_id = db.Column(db.String(50), info='任务编号')
task_status = db.Column(db.String(50), info='任务状态')
脚本如下,拿走不谢。
import re
import time
model_file = "../src/app/orm_vm_task/model.py" # 据实调整
def orm_class_header_gen(class_name, tb_name, keys, f_keys):
orm_str = "\n\nclass "+class_name+"Orm(object):\n" + \
" def __init__(self, curr_user = 'default'):\n" +\
" self.curr_user = curr_user\n" +\
" self.fields = %s\n"%str(keys)+ \
" self.ClassName = %s\n"%class_name
return orm_str
def orm_obj_to_dict(class_name, tb_name, keys, f_keys):
orm_str = "\n def _%s_obj2dict(self, obj):\n" % (re.sub(r"^t_", "", tb_name))
#注释
orm_str += ' """\n'
orm_str += ' 将对象统一转为字典\n'
orm_str += ' """\n'
orm_str += " return {\n"
for k in keys:
orm_str += " '%s': obj.%s,\n"%(k, k)
orm_str += " }\n"
return orm_str
def orm_qry_gen(class_name, tb_name, keys, f_keys):
#函数名称和参数
orm_str = "\n def get_%s_by_filter(self"%(re.sub(r"^t_", "", tb_name))
for k in keys:
if k=='id':
orm_str += ', t_id=None'
else:
orm_str += ", "+k+"=None"
for fk in f_keys:
orm_str += ", "+fk+"=None"
orm_str += "):\n"
#注释
orm_str += ' """\n'
orm_str += ' 根据给定的任意个参数,查询符合条件的记录\n'
orm_str += ' """\n'
#函数内容
orm_str += " filters = []\n"
for k in keys:
if k=='id':
orm_str += " if t_id is not None: filters.append(self.ClassName.id == t_id)\n"
else:
orm_str += " if "+k+" is not None: filters.append(self.ClassName.%s == %s)\n"%(k,k)
orm_str += " if filters:\n"
orm_str += " data_qry = self.ClassName.query.filter(and_(*filters)).all()\n"
orm_str += " else:\n"
orm_str += " data_qry = self.ClassName.query.all()\n"
orm_str += " data = []\n"
orm_str += " if data_qry:\n"
orm_str += " data = [self._%s_obj2dict(i) for i in data_qry]\n"%(re.sub(r"^t_", "", tb_name))
orm_str += " return data\n"
return orm_str
def orm_add_gen(class_name, tb_name, keys, f_keys):
orm_str = "\n def add_%s(self, info):"%(re.sub(r"^t_", "", tb_name))
orm_str += '''
"""
根据字典信息新增一条记录,返回id
"""
add_item = {}
for k in info:
if k in self.fields:
add_item[k] = info[k]
obj = self.ClassName(**add_item)
db.session.add(obj)
db.session.commit()
return obj.id
'''
return orm_str
def orm_upd_gen(class_name, tb_name, keys, f_keys):
orm_str = "\n def upd_%s(self, t_id, info):\n"%(re.sub(r"^t_", "", tb_name))
orm_str += '''
"""
更新字段信息
"""
upd_item = {}
for k in info:
if k in self.fields:
upd_item[k] = info[k]
self.ClassName.query.filter(self.ClassName.id == t_id).update(upd_item)
db.session.commit()
'''
return orm_str
def orm_del_gen(class_name, tb_name, keys, f_keys):
orm_str = "\n def del_%s(self, t_id):"%(re.sub(r"^t_", "", tb_name))
orm_str += '''
"""
删除记录
"""
self.ClassName.query.filter(self.ClassName.id == t_id).delete()
db.session.commit()
'''
return orm_str
def gen(class_name, tb_name, keys, f_keys):
if not class_name:
return ''
str_hd = orm_class_header_gen(class_name, tb_name, keys, f_keys)
str_dict = orm_obj_to_dict(class_name, tb_name, keys, f_keys)
str_qry = orm_qry_gen(class_name, tb_name, keys, f_keys)
str_add = orm_add_gen(class_name, tb_name, keys, f_keys)
str_upd = orm_upd_gen(class_name, tb_name, keys, f_keys)
str_del = orm_del_gen(class_name, tb_name, keys, f_keys)
return str_hd+ str_dict+ str_qry+ str_add+ str_upd+ str_del
def main():
model_lines = []
with open(model_file, 'r') as md:
model_lines = md.readlines()
class_name = ''
tb_name = ''
keys = []
f_keys = []
content = "from sqlalchemy import and_\nfrom xxx.models import *\nfrom xxx import db\n\n"
for line in model_lines:
if line.strip().startswith("#"):
continue
if line.find('db.Model')!=-1:
content += gen(class_name, tb_name, keys, f_keys) # 执行生成语句
class_name = line.replace('class','').replace('(db.Model):','').strip()
tb_name = ''
keys = []
f_keys = []
elif line.find('__tablename__')!=-1:
tb_name = line.replace('__tablename__','').replace('=','').replace("'",'').strip().lower()
elif line.find('db.Column')!=-1:
mat = re.search(r"\s*([\w_]+)\s*=", line)
if mat:
keys.append(mat.group(1))
content += gen(class_name, tb_name, keys, f_keys)
print(content)
if __name__ == "__main__":
main()
可将上述代码保存为orm_gen.py
,并执行python orm_gen.py
生成相关代码。(外键的相关功能暂未实现)