Flask框架下model和ORM通用函数代码生成

在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生成相关代码。（外键的相关功能暂未实现）