数据库反向查询
想运用request.GET.get(‘score_jieshoufankui2’, 100)来规避取不到值时的报错。
- views.py
# 建立数据反向查询的结果界面
@login_required
def data_search_results(request):
# 查询出user最大的query_set
user_permission_query = user_permission(request)
print('request.GET1',request.GET)
# 接收查询条件
if 'submit' in request.GET and request.GET['submit']:
# request.GET.get()和request.GET()区别
print('request.GET2',request.GET)
score_jieshoufankui1 = request.GET.get('score_jieshoufankui1', 0)
print(score_jieshoufankui1)
score_jieshoufankui2 = request.GET.get('score_jieshoufankui2', 100)
print(score_jieshoufankui2)
score_xueximinruidu1 = request.GET.get('score_xueximinruidu1', 0)
score_xueximinruidu2 = request.GET.get('score_xueximinruidu2', 100)
score_jieguodaoxiang1 = request.GET.get('score_jieguodaoxiang1', 0)
score_jieguodaoxiang2 = request.GET.get('score_jieguodaoxiang2', 100)
score_quanjusiwei1 = request.GET.get('score_quanjusiwei1', 0)
score_quanjusiwei2 = request.GET.get('score_quanjusiwei2', 100)
score_shiyingli1 = request.GET.get('score_shiyingli1', 0)
score_shiyingli2 = request.GET.get('score_shiyingli2', 100)
score_chengjiutaren1 = request.GET.get('score_chengjiutaren1', 0)
score_chengjiutaren2 = request.GET.get('score_chengjiutaren2', 100)
score_lingdaoyiyuan1 = request.GET.get('score_lingdaoyiyuan1', 0)
score_lingdaoyiyuan2 = request.GET.get('score_lingdaoyiyuan2', 100)
score_pinghengrenjiyurenwu1 = request.GET.get('score_pinghengrenjiyurenwu1', 0)
score_pinghengrenjiyurenwu2 = request.GET.get('score_pinghengrenjiyurenwu2', 100)
score_fudao1 = request.GET.get('score_fudao1', 0)
score_fudao2 = request.GET.get('score_fudao2', 100)
score_shouquanweize1 = request.GET.get('score_shouquanweize1', 0)
score_shouquanweize2 = request.GET.get('score_shouquanweize2', 100)
score_jianlichenggongtuandui1 = request.GET.get('score_jianlichenggongtuandui1', 0)
score_jianlichenggongtuandui2 = request.GET.get('score_jianlichenggongtuandui2', 100)
score_guanlirenjiguanxi1 = request.GET.get('score_guanlirenjiguanxi1', 0)
score_guanlirenjiguanxi2 = request.GET.get('score_guanlirenjiguanxi2', 100)
score_yingxiangli1 = request.GET.get('score_yingxiangli1', 0)
score_yingxiangli2 = request.GET.get('score_yingxiangli2', 100)
score_jianlihuobanguanxi1 = request.GET.get('score_jianlihuobanguanxi1', 0)
score_jianlihuobanguanxi2 = request.GET.get('score_jianlihuobanguanxi2', 100)
score_jihuayuzuzhi1 = request.GET.get('score_jihuayuzuzhi1', 0)
score_jihuayuzuzhi2 = request.GET.get('score_jihuayuzuzhi2', 100)
# 建立查询条件的字符串
str_list = Q(score_jieshoufankui__range=(score_jieshoufankui1,score_jieshoufankui2))
print(str_list)
str_list = str_list & Q(score_xueximinruidu__range=(score_xueximinruidu1,score_xueximinruidu2))
print(str_list)
str_list = str_list & Q(score_jieguodaoxiang__range=(score_jieguodaoxiang1,score_jieguodaoxiang2))
print(str_list)
str_list = str_list & Q(score_quanjusiwei__range=(score_quanjusiwei1,score_quanjusiwei2))
str_list = str_list & Q(score_shiyingli__range=(score_shiyingli1,score_shiyingli2))
str_list = str_list & Q(score_chengjiutaren__range=(score_chengjiutaren1,score_chengjiutaren2))
str_list = str_list & Q(score_lingdaoyiyuan__range=(score_lingdaoyiyuan1,score_lingdaoyiyuan2))
str_list = str_list & Q(score_pinghengrenjiyurenwu__range=(score_pinghengrenjiyurenwu1,score_pinghengrenjiyurenwu2))
str_list = str_list & Q(score_fudao__range=(score_fudao1,score_fudao2))
str_list = str_list & Q(score_shouquanweize__range=(score_shouquanweize1,score_shouquanweize2))
str_list = str_list & Q(score_jianlichenggongtuandui__range=(score_jianlichenggongtuandui1,score_jianlichenggongtuandui2))
str_list = str_list & Q(score_guanlirenjiguanxi__range=(score_guanlirenjiguanxi1,score_guanlirenjiguanxi2))
str_list = str_list & Q(score_yingxiangli__range=(score_yingxiangli1,score_yingxiangli2))
str_list = str_list & Q(score_jianlihuobanguanxi__range=(score_jianlihuobanguanxi1,score_jianlihuobanguanxi2))
str_list = str_list & Q(score_jihuayuzuzhi__range=(score_jihuayuzuzhi1,score_jihuayuzuzhi2))
employee_list = Employee.objects.filter(str_list) & user_permission_query
print(str_list)
return render(request, 'PPDASH/data_search_results.html',context={'employee_list': employee_list})
else:
return render(request, 'PPDASH/data_search_form.html',context={})
触发ValueError:Field ‘score_jieshoufankui’ expected a number but got ‘’.
后来打印 print(‘request.GET2’,request.GET),发现以下键值对的值是一个空列表,怪不得会显示’’。
request.GET2 <QueryDict: {'score_jieshoufankui1': [''], 'score_jieshoufankui2': [''], 'score_xueximinruidu1': [''], 'score_xueximinruidu2': [''], 'score_jieguodaoxiang1': [''], 'score_jieguodaoxiang2': [''], 'score_quanjusiwei1': [''], 'score_quanjusiwei2': [''], 'score_shiyingli1': [''], 'score_shiyingli2': [''], 'score_chengjiutaren1': [''], 'score_chengjiutaren2': [''], 'score_lingdaoyiyuan1': [''], 'score_lingdaoyiyuan2': [''], 'score_pinghengrenjiyurenwu1': [''], 'score_pinghengrenjiyurenwu2': [''], 'score_fudao1': [''], 'score_fudao2': [''], 'score_shouquanweize1': [''], 'score_shouquanweize2': [''], 'score_jianlichenggongtuandui1': [''], 'score_jianlichenggongtuandui2': [''], 'score_guanlirenjiguanxi1': [''], 'score_guanlirenjiguanxi2': [''], 'score_yingxiangli1': [''], 'score_yingxiangli2': [''], 'score_jianlihuobanguanxi1': [''], 'score_jianlihuobanguanxi2': [''], 'score_jihuayuzuzhi1': [''], 'score_jihuayuzuzhi2': [''], 'submit': ['查询']}>
视图views.py修正如下:
- views.py
# 建立数据反向查询的结果界面
@login_required
def data_search_results(request):
# 查询出user最大的query_set
user_permission_query = user_permission(request)
# 接收查询条件
if 'submit' in request.GET and request.GET['submit']:
# request.GET.get()避免KeyError
#print('request.GET2',request.GET)
score_jieshoufankui1 = request.GET.get('score_jieshoufankui1', '')
score_jieshoufankui2 = request.GET.get('score_jieshoufankui2', '')
score_xueximinruidu1 = request.GET.get('score_xueximinruidu1', '')
score_xueximinruidu2 = request.GET.get('score_xueximinruidu2', '')
score_jieguodaoxiang1 = request.GET.get('score_jieguodaoxiang1', '')
score_jieguodaoxiang2 = request.GET.get('score_jieguodaoxiang2', '')
score_quanjusiwei1 = request.GET.get('score_quanjusiwei1', '')
score_quanjusiwei2 = request.GET.get('score_quanjusiwei2', '')
score_shiyingli1 = request.GET.get('score_shiyingli1', '')
score_shiyingli2 = request.GET.get('score_shiyingli2', '')
score_chengjiutaren1 = request.GET.get('score_chengjiutaren1', '')
score_chengjiutaren2 = request.GET.get('score_chengjiutaren2', '')
score_lingdaoyiyuan1 = request.GET.get('score_lingdaoyiyuan1', '')
score_lingdaoyiyuan2 = request.GET.get('score_lingdaoyiyuan2', '')
score_pinghengrenjiyurenwu1 = request.GET.get('score_pinghengrenjiyurenwu1', '')
score_pinghengrenjiyurenwu2 = request.GET.get('score_pinghengrenjiyurenwu2', '')
score_fudao1 = request.GET.get('score_fudao1', '')
score_fudao2 = request.GET.get('score_fudao2', '')
score_shouquanweize1 = request.GET.get('score_shouquanweize1', '')
score_shouquanweize2 = request.GET.get('score_shouquanweize2', '')
score_jianlichenggongtuandui1 = request.GET.get('score_jianlichenggongtuandui1', '')
score_jianlichenggongtuandui2 = request.GET.get('score_jianlichenggongtuandui2', '')
score_guanlirenjiguanxi1 = request.GET.get('score_guanlirenjiguanxi1', '')
score_guanlirenjiguanxi2 = request.GET.get('score_guanlirenjiguanxi2', '')
score_yingxiangli1 = request.GET.get('score_yingxiangli1', '')
score_yingxiangli2 = request.GET.get('score_yingxiangli2', '')
score_jianlihuobanguanxi1 = request.GET.get('score_jianlihuobanguanxi1', '')
score_jianlihuobanguanxi2 = request.GET.get('score_jianlihuobanguanxi2', '')
score_jihuayuzuzhi1 = request.GET.get('score_jihuayuzuzhi1', '')
score_jihuayuzuzhi2 = request.GET.get('score_jihuayuzuzhi2', '')
# 增加默认值
if score_jieshoufankui1=='':score_jieshoufankui1=0
if score_jieshoufankui2=='':score_jieshoufankui2=100
if score_xueximinruidu1=='':score_xueximinruidu1=0
if score_xueximinruidu2=='':score_xueximinruidu2=100
if score_jieguodaoxiang1=='':score_jieguodaoxiang1=0
if score_jieguodaoxiang2=='':score_jieguodaoxiang2=100
if score_quanjusiwei1=='':score_quanjusiwei1=0
if score_quanjusiwei2=='':score_quanjusiwei2=100
if score_shiyingli1=='':score_shiyingli1=0
if score_shiyingli2=='':score_shiyingli2=100
if score_chengjiutaren1=='':score_chengjiutaren1=0
if score_chengjiutaren2=='':score_chengjiutaren2=100
if score_lingdaoyiyuan1=='':score_lingdaoyiyuan1=0
if score_lingdaoyiyuan2=='':score_lingdaoyiyuan2=100
if score_pinghengrenjiyurenwu1=='':score_pinghengrenjiyurenwu1=0
if score_pinghengrenjiyurenwu2=='':score_pinghengrenjiyurenwu2=100
if score_fudao1=='':score_fudao1=0
if score_fudao2=='':score_fudao2=100
if score_shouquanweize1=='':score_shouquanweize1=0
if score_shouquanweize2=='':score_shouquanweize2=100
if score_jianlichenggongtuandui1=='':score_jianlichenggongtuandui1=0
if score_jianlichenggongtuandui2=='':score_jianlichenggongtuandui2=100
if score_guanlirenjiguanxi1=='':score_guanlirenjiguanxi1=0
if score_guanlirenjiguanxi2=='':score_guanlirenjiguanxi2=100
if score_yingxiangli1=='':score_yingxiangli1=0
if score_yingxiangli2=='':score_yingxiangli2=100
if score_jianlihuobanguanxi1=='':score_jianlihuobanguanxi1=0
if score_jianlihuobanguanxi2=='':score_jianlihuobanguanxi2=100
if score_jihuayuzuzhi1=='':score_jihuayuzuzhi1=0
if score_jihuayuzuzhi2=='':score_jihuayuzuzhi2=100
# 建立查询条件的字符串
str_list = Q(score_jieshoufankui__range=(score_jieshoufankui1,score_jieshoufankui2))
str_list = str_list & Q(score_xueximinruidu__range=(score_xueximinruidu1,score_xueximinruidu2))
str_list = str_list & Q(score_jieguodaoxiang__range=(score_jieguodaoxiang1,score_jieguodaoxiang2))
str_list = str_list & Q(score_quanjusiwei__range=(score_quanjusiwei1,score_quanjusiwei2))
str_list = str_list & Q(score_shiyingli__range=(score_shiyingli1,score_shiyingli2))
str_list = str_list & Q(score_chengjiutaren__range=(score_chengjiutaren1,score_chengjiutaren2))
str_list = str_list & Q(score_lingdaoyiyuan__range=(score_lingdaoyiyuan1,score_lingdaoyiyuan2))
str_list = str_list & Q(score_pinghengrenjiyurenwu__range=(score_pinghengrenjiyurenwu1,score_pinghengrenjiyurenwu2))
str_list = str_list & Q(score_fudao__range=(score_fudao1,score_fudao2))
str_list = str_list & Q(score_shouquanweize__range=(score_shouquanweize1,score_shouquanweize2))
str_list = str_list & Q(score_jianlichenggongtuandui__range=(score_jianlichenggongtuandui1,score_jianlichenggongtuandui2))
str_list = str_list & Q(score_guanlirenjiguanxi__range=(score_guanlirenjiguanxi1,score_guanlirenjiguanxi2))
str_list = str_list & Q(score_yingxiangli__range=(score_yingxiangli1,score_yingxiangli2))
str_list = str_list & Q(score_jianlihuobanguanxi__range=(score_jianlihuobanguanxi1,score_jianlihuobanguanxi2))
str_list = str_list & Q(score_jihuayuzuzhi__range=(score_jihuayuzuzhi1,score_jihuayuzuzhi2))
employee_list = Employee.objects.filter(str_list) & user_permission_query
print(str_list)
return render(request, 'PPDASH/data_search_results.html',context={'employee_list': employee_list})
else:
return render(request, 'PPDASH/data_search_form.html',context={})
- data_search_form.html
{% extends 'PPDASH/base.html' %}
{% load static %}
{% block title_block %}
search_form
{% endblock %}
{% block body_block %}
<div class="container">
<div class="row py-4 align-items-center">
<form action="/PPDASH/data_search_results/" method="get">
<label class="row">
<div class="col-6">
输入查询条件
低值
高值
</div>
<div class="col-6">
低值
高值
</div>
</label>
<div class="row">
<div class="col-6">
<div class="form-group row">
<label class="col-3">接纳反馈</label>
<input type="number" name="score_jieshoufankui1" value='0' class="col-2">
<input type="number" name="score_jieshoufankui2" value='100'class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">学习敏锐度</label>
<input type="number" name="score_xueximinruidu1" value='0' class="col-2">
<input type="number" name="score_xueximinruidu2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">结果导向</label>
<input type="number" name="score_jieguodaoxiang1" value='0' class="col-2">
<input type="number" name="score_jieguodaoxiang2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">全局思维</label>
<input type="number" name="score_quanjusiwei1" value='0' class="col-2">
<input type="number" name="score_quanjusiwei2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">适应力</label>
<input type="number" name="score_shiyingli1" value='0' class="col-2">
<input type="number" name="score_shiyingli2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">成就他人</label>
<input type="number" name="score_chengjiutaren1" value='0' class="col-2">
<input type="number" name="score_chengjiutaren2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">领导意愿</label>
<input type="number" name="score_lingdaoyiyuan1" value='0' class="col-2">
<input type="number" name="score_lingdaoyiyuan2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">平衡人际与任务</label>
<input type="number" name="score_pinghengrenjiyurenwu1" value='0' class="col-2">
<input type="number" name="score_pinghengrenjiyurenwu2" value='100' class="col-2 mx-1">
</div>
</div>
<div class="col-6">
<div class="form-group row">
<label class="col-3">辅导</label>
<input type="number" name="score_fudao1" value='0' class="col-2">
<input type="number" name="score_fudao2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">授权委责</label>
<input type="number" name="score_shouquanweize1" value='0' class="col-2">
<input type="number" name="score_shouquanweize2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">建立成功团队</label>
<input type="number" name="score_jianlichenggongtuandui1" value='0' class="col-2">
<input type="number" name="score_jianlichenggongtuandui2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">管理人际关系</label>
<input type="number" name="score_guanlirenjiguanxi1" value='0' class="col-2">
<input type="number" name="score_guanlirenjiguanxi2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">影响力</label>
<input type="number" name="score_yingxiangli1" value='0' class="col-2">
<input type="number" name="score_yingxiangli2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">建立伙伴关系</label>
<input type="number" name="score_jianlihuobanguanxi1" value='0' class="col-2">
<input type="number" name="score_jianlihuobanguanxi2" value='100' class="col-2 mx-1">
</div>
<div class="form-group row">
<label class="col-3">计划与组织</label>
<input type="number" name="score_jihuayuzuzhi1" value='0' class="col-2">
<input type="number" name="score_jihuayuzuzhi2" value='100' class="col-2 mx-1">
</div>
</div>
</div>
<input type="submit" name="submit" value="查询">
</form>
</div>
</div>
{% endblock %}
- data_search_results.html
{% extends 'PPDASH/base.html' %}
{% load static %}
{% block title_block %}
search_results
{% endblock %}
{% block body_block %}
<!-- 调整color的js脚本开始 -->
<script>
window.onload = function(){
paradom1 = document.getElementsByClassName("p1")
console.log(paradom1)
for (i = 0 ; i < paradom1.length; i++) {
var para = document.getElementsByClassName("p1")[i];if (para.textContent <=35) {para.style.color = "red";}else if (para.textContent <65) {para.style.color = "orange";}else{para.style.color = "green";}
}
}
</script>
<!-- 调整color的js脚本介绍 -->
<div class="container-fluid">
<p class="my-3">
查询结果如下
<a href="/PPDASH/compare_by_different_employee/{{ employee_list }}/" >个人对比图(前20名)</a>
</p>
<table class="table table-striped table-bordered">
<thead>
<tr style="color:White;background-color:#3366FF;font-family:微軟正黑體,Tahoma,Arial,微軟雅黑體;font-size:10px;">
<th scope="col">工號</th>
<th scope="col" style="width:80px;">姓名</th>
<th scope="col">部門</th>
<th scope="col">Level</th>
<th scope="col">Function</th>
<th scope="col">批次</th>
<th scope="col">接纳反馈</th>
<th scope="col">学习敏锐度</th>
<th scope="col">结果导向</th>
<th scope="col">全局思维</th>
<th scope="col">适应力</th>
<th scope="col">成就他人</th>
<th scope="col">领导意愿</th>
<th scope="col">平衡人际与任务</th>
<th scope="col">辅导*</th>
<th scope="col">授权委责*</th>
<th scope="col">建立成功团队*</th>
<th scope="col">管理人际关系*</th>
<th scope="col">影响力*</th>
<th scope="col">建立伙伴关系*</th>
<th scope="col">计划与组织*</th>
</tr>
</thead>
{% for employee in employee_list %}
<tr valign="middle" style="color:Black;border-color:#E0E0E0;font-size:10px;">
<td><a href="/PPDASH/search_results/{{ employee.eid }}/">{{ employee.eid }}</a></td>
<td>{{ employee.name }}</td>
<td>{{ employee.dept_code }}</td>
<td>{{ employee.level }}</td>
<td>{{ employee.function }}</td>
<td>{{ employee.batch }}</td>
<td class="p1">{{ employee.score_jieshoufankui }}</td>
<td class="p1">{{ employee.score_xueximinruidu }}</td>
<td class="p1">{{ employee.score_jieguodaoxiang }}</td>
<td class="p1">{{ employee.score_quanjusiwei }}</td>
<td class="p1">{{ employee.score_shiyingli }}</td>
<td class="p1">{{ employee.score_chengjiutaren }}</td>
<td class="p1">{{ employee.score_lingdaoyiyuan }}</td>
<td class="p1">{{ employee.score_pinghengrenjiyurenwu }}</td>
<td class="p1">{{ employee.score_fudao }}</td>
<td class="p1">{{ employee.score_shouquanweize }}</td>
<td class="p1">{{ employee.score_jianlichenggongtuandui }}</td>
<td class="p1">{{ employee.score_guanlirenjiguanxi }}</td>
<td class="p1">{{ employee.score_yingxiangli }}</td>
<td class="p1">{{ employee.score_jianlihuobanguanxi }}</td>
<td class="p1">{{ employee.score_jihuayuzuzhi }}</td>
</tr>
{% endfor %}
</table>
</div>
<!--信息为不多时的留白-->
{% if not employee_list|length >= 3 %}
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
{% endif %}
{% endblock %}