Django中使用MySQL的视图View

一、MySQL创建视图

首先确定好自己需要链接的两张表及其对应的字段，视图的意义是将两张表数据联合一起变成一张新的表方便后续查询数据。

源Django两张表模型

	class ProjectTaskRange(BaseModel, TimeModel):
	    class Meta:
	        verbose_name = "任务安排"
	        verbose_name_plural = "任务安排"
	
	    project_case_id = models.ForeignKey(
	        help_text="所属项目实例ID", verbose_name="所属项目实例ID", to='ProjectCase', to_field='id',
	        db_column='project_case_id', on_delete=models.CASCADE, default=0, null=True
	    )
	
	    project_case_step_quote_id = models.ForeignKey(
	        verbose_name="所属项目实施步骤ID", to='ProjectCaseStepQuote', help_text="所属项目实施步骤ID",
	        to_field='id', db_column='project_case_step_quote_id', on_delete=models.CASCADE, default=0, null=True
	    )
	
	    project_task_range_name = models.CharField(help_text="任务安排名称", verbose_name="任务安排名称", default='',
	                                               max_length=256)
	
	    project_task_manager = models.ForeignKey(
	        verbose_name="任务负责人ID", help_text="任务负责ID", related_name="tasks",
	        to="User", to_field="id", db_column="project_task_manager",
	        on_delete=models.PROTECT, null=True, blank=True, default=1
	    )
	
	    project_task_executor = models.ManyToManyField(User, default=None)
	
	    executor_coefficient = models.FloatField(verbose_name="人员系数", help_text="人员系数", default=1)
	
	    step_id = models.SmallIntegerField(verbose_name='区分step_Id', default=None, null=True)
	
	    range_description = models.CharField(max_length=256, verbose_name='任务不合理描述', null=True, blank=True)
	
	    overdue_choices = (
	        (0, None),
	        (1, "完成"),
	        (2, "逾期未做任务"),
	        (3, "逾期在做任务")
	    )
	    overdue = models.SmallIntegerField(verbose_name='逾期标识', choices=overdue_choices, null=True, blank=True,
	                                       default=0)
	
	    mount = models.SmallIntegerField(verbose_name='挂载引用步骤顺序', null=True, blank=True)
	
	    check_time = models.DateTimeField(verbose_name='审核完成日期', null=True, default=None)       # 根据状态自动生成
	
	    project_id = models.SmallIntegerField(verbose_name='标准工时大列id', null=True, default=None)
	
	    # 任务审核人 查询引用步骤是否已有审核人， 有则添加， 无则添加任务责任人。
	    range_auditor = models.CharField(verbose_name='任务审核人id', max_length=128, null=True, default=None)
	    # 任务备注
	    task_description = models.TextField(max_length=65335, verbose_name='任务描述', null=True, blank=True)
	
	    # 0 待审核 1审核中 2审核完 3 不通过
	    status_choices = {
	        (0, "待审核"),
	        (1, "审核中"),
	        (2, "审核完成"),
	        (3, "审核不通过"),
	    }
	
	    range_check_status = models.SmallIntegerField(verbose_name='任务审核状态', choices=status_choices, null=True, blank=True,
	                                       default=0)
	
	    execute_choices = {
	        (0, "未执行"),
	        (1, "执行中"),
	        (2, "执行完成"),
	    }
	
	    range_execute_status = models.SmallIntegerField(verbose_name='任务审核状态', choices=execute_choices, null=True, blank=True,default=0)


	class TaskRangeFile(BaseModel):
	    class Meta:
	        verbose_name = "任务完成情况附件"
	        verbose_name_plural = "任务完成情况附件"
	
	    project_task_range_id = models.ForeignKey(verbose_name="所属的任务安排ID", to="ProjectTaskRange", to_field="id",
	                                              related_name="files",
	                                              db_column='project_task_range_id', on_delete=models.CASCADE, null=True)
	    # 用户上传的文件
	    file = models.FileField(verbose_name="文件", upload_to="task_range_files/", null=True)
	    # 标准附件
	    step_file = models.FileField(verbose_name='任务模板步骤附件', upload_to="task_quote_file/", null=True)
	    step_description = models.FileField(verbose_name='任务模板步骤描述', max_length=128, null=True)
	    step_id = models.SmallIntegerField(verbose_name='标准附件id', null=True, default=0)
	    # 任务模版附件
	    range_file = models.FileField(verbose_name='任务模版附件', upload_to='range_file', null=True)
	    # 是否上传附件  标准工时通过到这
	    true_or_false = models.BooleanField(verbose_name='是否必须上传标准附件', null=True, default=None)
	    # 任务审核状态是否通过
	    file_true_or_false = models.BooleanField(verbose_name='审核状态是否通过', null=True, default=None)
	    parameter = models.CharField(max_length=256, verbose_name='参数', null=True, blank=True)
	    # 参数描述
	    parameter_description = models.CharField(max_length=256, verbose_name='附件描述', null=True, blank=True)
	    # 参数是否必填
	    parameter_true_or_false = models.BooleanField(verbose_name='参数是否必填', null=True, default=False)

以上是演示的两张表原型，View的新表字段是通过我们自己选择性创建的，我们需要哪些字段可以加入哪些字段。

SQL创建view（注意字段名称不要重复 以免报错）

	SELECT
		`app02_projecttaskrange`.`id` AS `range_id`,
		`app02_projecttaskrange`.`create_by` AS `range_create_by`,
		`app02_projecttaskrange`.`create_time` AS `range_create_time`,
		`app02_projecttaskrange`.`update_by` AS `range_update_by`,
		`app02_projecttaskrange`.`update_time` AS `range_update_time`,
		`app02_projecttaskrange`.`delete` AS `range_delete`,
		`app02_projecttaskrange`.`plan_start_time` AS `range_plan_start_time`,
		`app02_projecttaskrange`.`plan_stop_time` AS `range_plan_stop_time`,
		`app02_projecttaskrange`.`actual_start_time` AS `range_actual_start_time`,
		`app02_projecttaskrange`.`actual_stop_time` AS `range_actual_stop_time`,
		`app02_projecttaskrange`.`project_task_range_name` AS `range_project_task_range_name`,
		`app02_projecttaskrange`.`executor_coefficient` AS `range_executor_coefficient`,
		`app02_projecttaskrange`.`project_case_id` AS `range_project_case_id`,
		`app02_projecttaskrange`.`project_task_manager` AS `range_project_task_manager`,
		`app02_projecttaskrange`.`project_case_step_quote_id` AS `range_project_case_step_quote_id`,
		`app02_projecttaskrange`.`step_id` AS `range_step_id`,
		`app02_projecttaskrange`.`range_description` AS `range_description`,
		`app02_projecttaskrange`.`mount` AS `range_mount`,
		`app02_projecttaskrange`.`overdue` AS `range_overdue`,
		`app02_projecttaskrange`.`check_time` AS `range_check_time`,
		`app02_projecttaskrange`.`project_id` AS `range_project_id`,
		`app02_projecttaskrange`.`range_auditor` AS `range_auditor`,
		`app02_projecttaskrange`.`task_description` AS `range_task_description`,
		`app02_projecttaskrange`.`range_check_status` AS `range_check_status`,
		`app02_projecttaskrange`.`range_execute_status` AS `range_execute_status`,
		`app02_taskrangefile`.`id` AS `id`,
		`app02_taskrangefile`.`create_by` AS `file_create_by`,
		`app02_taskrangefile`.`create_time` AS `file_create_time`,
		`app02_taskrangefile`.`update_by` AS `file_update_by`,
		`app02_taskrangefile`.`update_time` AS `file_update_time`,
		`app02_taskrangefile`.`delete` AS `file_delete`,
		`app02_taskrangefile`.`file` AS `file`,
		`app02_taskrangefile`.`project_task_range_id` AS `file_project_task_range_id`,
		`app02_taskrangefile`.`step_description` AS `file_step_description`,
		`app02_taskrangefile`.`step_file` AS `file_step_file`,
		`app02_taskrangefile`.`step_id` AS `file_step_id`,
		`app02_taskrangefile`.`range_file` AS `file_range_file`,
		`app02_taskrangefile`.`true_or_false` AS `file_true_or_false`,
		`app02_taskrangefile`.`file_true_or_false` AS `file_file_true_or_false`,
		`app02_taskrangefile`.`parameter` AS `file_parameter`,
		`app02_taskrangefile`.`parameter_description` AS `file_parameter_description`,
		`app02_taskrangefile`.`parameter_true_or_false` AS `file_parameter_true_or_false` 
	FROM
		(
			`app02_projecttaskrange`
			JOIN `app02_taskrangefile` ON ((
					`app02_projecttaskrange`.`id` = `app02_taskrangefile`.`project_task_range_id` 
				))) 
	WHERE
		((
				`app02_projecttaskrange`.`delete` <> 1 
			) 
		AND ( `app02_taskrangefile`.`delete` <> 1 ))

Navicat创建视图
1. 选择好数据库，右键视图创建视图

2. 可以通过视图创建工具选择对应表及其对应的字段创建即可（底层也是SQL创建）

3.以上只是在MySQL中定义了view视图，但是Django中是没有这个view

二、Django模型定义

注意Django定义的模型名称需要跟上一步MySQL中的view名称一致，MySQL中view是什么Django对应即可

models.py

class RangeAndFIle(models.Model):
    range_id = models.SmallIntegerField()
    range_create_by = models.CharField(max_length=128)
    range_create_time = models.DateTimeField()
    range_update_by = models.CharField(max_length=128)
    range_update_time = models.DateTimeField()
    range_delete = models.BooleanField()
    range_plan_start_time = models.DateTimeField()
    range_plan_stop_time = models.DateTimeField()
    range_actual_start_time = models.DateTimeField()
    range_actual_stop_time = models.DateTimeField()
    range_project_task_range_name = models.CharField(max_length=128)
    range_executor_coefficient = models.FloatField()
    range_project_case_id = models.SmallIntegerField()
    range_project_task_manager = models.SmallIntegerField()
    range_project_case_step_quote_id = models.SmallIntegerField()
    range_step_id = models.SmallIntegerField()
    range_description = models.TextField(max_length=256)
    range_mount = models.SmallIntegerField()
    range_overdue = models.SmallIntegerField()
    range_check_time = models.DateTimeField()
    range_project_id = models.SmallIntegerField()
    range_auditor = models.CharField(max_length=32)
    range_task_description = models.TextField(max_length=65335)
    range_check_status = models.SmallIntegerField()
    range_execute_status = models.SmallIntegerField()
    id =  models.SmallIntegerField(primary_key=True)	# 注意唯一ID还是需要的
    file_create_by =  models.CharField(max_length=32)
    file_create_time = models.DateTimeField()
    file_update_by =  models.CharField(max_length=32)
    file_update_time = models.DateTimeField()
    file_delete = models.BooleanField()
    file = models.CharField(max_length=256)
    file_project_task_range_id = models.SmallIntegerField()
    file_step_description = models.CharField(max_length=256)
    file_step_file = models.CharField(max_length=256)
    file_step_id =  models.SmallIntegerField()
    file_range_file = models.CharField(max_length=256)
    file_true_or_false = models.BooleanField()
    file_file_true_or_false = models.BooleanField()
    file_parameter = models.CharField(max_length=256)
    file_parameter_description = models.CharField(max_length=256)
    file_parameter_true_or_false = models.BooleanField()
    class Meta:
        managed = False		# 默认是Ture 设成False django将不会执行建表和删表操作,但会生成迁移文件

        db_table = 'range_and_file'		# 注意对应MySQL中的名称

表字段及其对应关系写好之后执行命令

	python manage.py makemigrations
	
	python manage.py migrate

这样视图就创建好了可以通过view是写对应的接口查询数据，注意view视图只能查看不能做修改增加删除功能

views.py

	class GetMergedTaskData(BaseViewSet):
	    queryset = RangeAndFIle.objects.filter(range_delete=False).order_by('-range_create_time')
	    serializer_class = RangeAndFIleSerializer
	    filter_backends = [MyFilterBackend,  RangeAndFIleBackend]
	    filterset_class = RangeAndFIleFilter
	
	    def get_queryset(self):
	        queryset = super().get_queryset()
	
	        query_params = {key: value for key, value in self.request.query_params.items() if value != ''}
	
	        project_task_executor = query_params.get('project_task_executor')
	        range_auditor = query_params.get('range_auditor')
	        range_project_task_manager = query_params.get('range_project_task_manager')
	
	        if 'ordering' in query_params:
	            if query_params['ordering'] == '-create_time':
	                queryset = queryset.filter(range_delete=False).order_by('-range_create_time')
	            else:
	                queryset = queryset.filter(range_delete=False).order_by('range_create_time')
	
	        if project_task_executor:
	            result = ProjectTaskRange.objects.filter(project_task_executor=int(project_task_executor), delete=False).all()
	            id_list = [ i.id for i in result]
	            queryset = queryset.filter(range_id__in=id_list, range_delete=False).all()
	
	        if 'file_file_true_or_false' in query_params:
	            if self.request.query_params['file_file_true_or_false'] == 'none':
	                queryset = queryset.filter(range_delete=False, file_file_true_or_false=None).order_by('range_create_time')
	            elif self.request.query_params['file_file_true_or_false'] == 'true':
	                queryset = queryset.filter(range_delete=False, file_file_true_or_false=True).order_by('range_create_time')
	            else:
	                queryset = queryset.filter(range_delete=False, file_file_true_or_false=False).order_by('range_create_time')
	
	        filters = {}
	        for field_name in ['range_check_time']:
	            after = self.request.query_params.get(f'{field_name}_after')
	            before = self.request.query_params.get(f'{field_name}_before')
	
	            if after and before:
	                filters[f'{field_name}__gte'] = before
	                filters[f'{field_name}__lte'] = after
	
	        if range_project_task_manager and range_project_task_manager != '':
	            filters['range_project_task_manager'] = range_project_task_manager
	
	        if range_auditor and range_auditor != '':
	            filters['range_auditor__contains'] = range_auditor
	
	        if filters:
	            queryset = queryset.filter(**filters)
	        return queryset

以上是文章所有内容 希望帮助到你