与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()
注:
- 查询时可传递任意字段的组合作为参数,无需定制化地传递参数,结果统一返回queryset(可遍历)
- 在新增记录时,普通字段统一放在字典里传参,外键字段则统一直接作为参数传递;
- common_queryset_archive_to_history为另外编写的通用归档至历史表的函数,如不需要可删除此调用;