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

与flask类似，在Django中，也同样有python manage.py migrate命令可将model映射到数据库中，但是在实际工程实践中，往往是先通过PowerDesigner、PDManer等软件进行数据库表的结构设计，此时可以通过此类软件直接导出数据库的建表语句，从而创建数据库表。

对此，参照上一文中的场景，我们同样需要一套机制从数据库表反向映射生成model，并生成最基本的增删改查语句，以简化大量的增删改查语句的Ctrl C/Ctrl V操作。

SQL到model

python manage.py inspectdb --setting=config.settings.myconfig > models.table

功能：从配置文件里面设置的数据库中，生成所有的models类，其中setting指定所使用的Django配置文件，包括数据库配置等内容。

注：生成的model只有基础的字段和类型，对于外键需要用户自行调整。

model到ORM(DAO)

样例model

class NetworkFirewallPolicy(models.Model):
    """
        防火墙策略表
    """
    id = models.AutoField(primary_key=True)
    identifier = models.CharField(blank=True, null=True, max_length=100)
    src_zone = models.CharField(blank=True, null=True, max_length=300)
    src_ip = models.CharField(blank=True, null=True, max_length=2048)
    dst_zone = models.CharField(blank=True, null=True, max_length=300)
    dst_ip = models.CharField(blank=True, null=True, max_length=2048)
    service = models.CharField(blank=True, null=True, max_length=1024)
    action = models.CharField(blank=True, null=True, max_length=100)
    policy_group = models.CharField(blank=True, null=True, max_length=100)
    policy_status = models.CharField(blank=True, null=True, max_length=100) 
    fw = models.ForeignKey(NetworkFirewallDevice, models.DO_NOTHING, db_column='fw_id')

    class Meta:
        managed = False
        db_table = 'NETWORK_FIREWALL_POLICY'

生成脚本

import re
import time
model_file = "./xxx/xxx/models.py"    # 自行修改



def dao_qry_gen(class_name, tb_name, keys, f_keys):
    # print(class_name, tb_name, keys, f_keys)
    #类定义和__init__
    dao_str = "class "+class_name+"DAO(object):\n" + \
            "    def __init__(self, curr_user = 'default', archive_timestamp=None):\n" +\
            "        self.curr_user = curr_user\n" +\
            '        if archive_timestamp is None: self.archive_timestamp = time.strftime("%Y%m%d%H%M%S")\n\n'
    
    #函数名称和参数
    dao_str += "    def get_"+tb_name+"_by_filter(self"
    for k in keys:
        if k=='id':
            dao_str += ', table_id=None'
        else:
            dao_str += ", "+k+"=None"
    for fk in f_keys:
        dao_str += ", "+fk+"=None"
    dao_str += "):\n"
    #注释
    dao_str += '        """\n'
    dao_str += '            描述：\n'
    dao_str += '                根据给定的任意个参数，获取并返回对应的queryset\n'
    dao_str += '            修改历史：\n'
    dao_str += '                create by xxxxxxxxx %s\n'%(time.strftime("%Y%m%d"))
    dao_str += '        """\n'

    #函数内容
    dao_str += "        Q_list = []\n"
    for k in keys+f_keys:
        if k=='id':
            dao_str += "        if table_id is not None: Q_list.append(Q(id = table_id))\n"
        else:
            dao_str += "        if "+k+" is not None: Q_list.append(Q(" + k +"=" + k +"))\n"
    dao_str += "        if Q_list:\n"
    dao_str += "            return "+class_name+".objects.filter(reduce(operator.and_, Q_list))\n"
    dao_str += "        else:\n"
    dao_str += "            return "+class_name+".objects.all()\n"
    return dao_str

def dao_add_gen(class_name, tb_name, keys, f_keys):
    dao_str = "\n    def add_"+tb_name+"(self"
    has_other_keys_except_id = False
    for k in keys:
        if k=='id':
            continue
        else:
            has_other_keys_except_id = True
            break
    if has_other_keys_except_id:
        dao_str += ', info_dict:Dict'
    
    for fk in f_keys:
        dao_str += ", "+fk+"=None"
    dao_str += "):\n"
    #注释
    dao_str += '        """\n'
    dao_str += '            描述：\n'
    dao_str += '                通过输入的字典值和各外键值，创建%s表的新记录\n'%class_name
    dao_str += '            修改历史：\n'
    dao_str += '                create by xxxxxxxxx %s\n'%(time.strftime("%Y%m%d"))
    dao_str += '        """\n'

    #函数内容
    #2.1 create
    if len(keys)>=2:
        dao_str += "        obj = "+class_name +".objects.create("+keys[1]+"=info_dict['"+keys[1]+"']"
        for k in f_keys:
            dao_str += ", "+k+"="+k
        dao_str += ")\n"
    else:
        dao_str += "        obj = "+class_name +".objects.create("
        for k in f_keys:
            dao_str += ", "+k+"="+k
        dao_str += ")\n"
    #2.2 update
    dao_str += "        if obj:\n"
    for k in keys:
        if k=='id':
            continue
        dao_str += "            if '%s' in info_dict: obj.%s=info_dict['%s']\n"%(k,k,k)
    for k in f_keys:
        dao_str += "            if %s: obj.%s=%s\n"%(k,k,k)
    dao_str += "            obj.save()\n"
    dao_str += "        return obj\n"
    return dao_str

def dao_upd_gen(class_name, tb_name, keys, f_keys):
    dao_str = "\n    def upd_"+tb_name+"(self, table_id, info_dict:Dict):\n"
    
    #注释
    dao_str += '        """\n'
    dao_str += '            描述：\n'
    dao_str += '                通过输入id，查找%s表记录并将其修改为info_dict中的内容\n'%class_name
    dao_str += '            修改历史：\n'
    dao_str += '                create by xxxxxxxxx %s\n'%(time.strftime("%Y%m%d"))
    dao_str += '        """\n'

    #函数内容
    dao_str += "        obj = %s.objects.get(id=table_id)\n"%(class_name)
    dao_str += "        if obj:\n"
    #保存历史
    dao_str += "            #保存历史\n"
    dao_str += '            common_queryset_archive_to_history(obj, His%s, "CRUD", self.archive_timestamp, self.curr_user, "UPDATE")\n\n'%(class_name)

    #修改
    for k in keys + f_keys:
        if k=="id":
            continue
        dao_str += "            if '%s' in info_dict: obj.%s = info_dict['%s']\n"%(k,k,k)
    dao_str += "            obj.save()\n"
    dao_str += "        return obj\n"
    # print(dao_str)
    return dao_str

def dao_del_gen(class_name, tb_name, keys, f_keys):
    dao_str = "\n    def del_"+tb_name+"(self, table_id=None"
    for fk in f_keys:
        dao_str += ", "+fk+"=None"
    dao_str += "):\n"
    
    #注释
    dao_str += '        """\n'
    dao_str += '            描述：\n'
    dao_str += '                通过输入的参数，查找%s表记录并将其删除\n'%class_name
    dao_str += '            修改历史：\n'
    dao_str += '                create by xxxxxxxxx %s\n'%(time.strftime("%Y%m%d"))
    dao_str += '        """\n'

    #函数内容
    dao_str += "        #查询\n"
    dao_str += "        objs = self.get_%s_by_filter(table_id=table_id"%tb_name
    for k in f_keys:
        dao_str += ", "+k+"="+k
    dao_str += ")\n"

    #保存历史
    dao_str += "        #保存历史\n"
    dao_str += '        common_queryset_archive_to_history(objs, His%s, "CRUD", self.archive_timestamp, self.curr_user, "DELETE")\n'%(class_name)
    #delete
    dao_str += "        #执行删除\n"
    dao_str += "        objs.delete()\n"
    # print(dao_str)
    return dao_str

def main():
    model_lines = []
    with open(model_file, 'r') as md:
        model_lines = md.readlines()

    in_class = False
    keys = []
    f_keys = []
    for line in model_lines:
        if line.strip().startswith("#"):
            continue
        if line.find('models.Model')!=-1:
            class_name = line.replace('class','').replace('(models.Model):','').strip()
            tb_name = ''
            in_class = True
            keys = []
            f_keys = []

        elif line.find('db_table')!=-1:
            in_class = False
            tb_name = line.replace('db_table','').replace('=','').replace("'",'').strip().lower()
            str_qry = dao_qry_gen(class_name, tb_name, keys, f_keys)
            str_add = dao_add_gen(class_name, tb_name, keys, f_keys)
            str_upd = dao_upd_gen(class_name, tb_name, keys, f_keys)
            str_del = dao_del_gen(class_name, tb_name, keys, f_keys)
            print(str_qry, str_add, str_upd, str_del)
        if in_class:
            mat_fk = re.search(r"\s*(\w+)\s*=\s*models.ForeignKey", line)
            if line.find('managed =')!=-1:
                continue
            elif mat_fk:
                if mat_fk.group(1).endswith('_id'):
                    f_keys.append(mat_fk.group(1))
                else:
                    f_keys.append(mat_fk.group(1)+'_id')
            else:
                mat = re.search(r"\s*(\w+)\s*=", line)
                if mat:
                    keys.append(mat.group(1))
        
        else:
            continue

if __name__ == "__main__":
    main()

注：

1. 查询时可传递任意字段的组合作为参数，无需定制化地传递参数，结果统一返回queryset（可遍历）
2. 在新增记录时，普通字段统一放在字典里传参，外键字段则统一直接作为参数传递；
3. common_queryset_archive_to_history为另外编写的通用归档至历史表的函数，如不需要可删除此调用；