django(3) 一对多跨表查询、ajax、多对多

时间:2022-11-06 14:21:48

1.一对多跨表查询获取数据的三种形式:对象、字典、元组

  例:有host与business两张表,host与business的id字段关联,business在host表中的对象名是b,  通过查询host表获取的querySet中的b来查询business表。

  以下两种方式都是操作host表:

  a.以对象的形式跨表查询,查询business表的数据:row.b.caption,row.b.id

  b.以字典和元组的形式跨表查询,查询business表的数据,跨表查询的时候要用双下划线  'b__id','b__caption', # 这里caption是关联表business的字段

views.py

def host(request):
    #三种形式的跨表查询(这里有host与business两张表,host与business的id字段关联,business在host表中的对象名是b,
    # 通过查询host表获取的querySet来查询business表)
    v1 = models.Host.objects.filter(nid__gt=0)
    for row in v1:
        #v1以对象的形式跨表查询row.b.caption,       ,row.b.id
        print(row.nid,row.hostname,row.ip,row.port,row.b.caption,row.b.id)

    v2 = models.Host.objects.all().values('nid','hostname','b_id','b__caption',)
    for row2 in v2:
        # v2以字典的形式跨表查询,跨表查询(即查询另一个关联表的数据)的时候要用双下划线'b__caption',这里caption是关联表的字段
        print(row2)  #结果是字典  {'nid': 8, 'hostname': 'ee', 'b_id': 5, 'b__caption': '运维'}
        print(row2['nid'],row2['hostname'],row2['b_id'],row2['b__caption'])

    v3 = models.Host.objects.all().values_list('nid','hostname','b_id','b__caption')
    for row3 in v3:
        # v3以元组的形式跨表查询,跨表查询(即查询另一个关联表的数据)的时候要用双下划线'b__caption',这里caption是关联表的字段
        print(row3)#结果是元组  (7, 'wt', 6, '开发')
        print(row3[0],row3[1],row3[2],row3[3])

    if request.method == 'GET':
        v1 = models.Host.objects.filter(nid__gt=0)
        v2 = models.Host.objects.all().values('nid', 'hostname', 'b_id', 'b__caption', )
        v3 = models.Host.objects.all().values_list('nid', 'hostname', 'b_id', 'b__caption')
        b_list = models.Business.objects.all()
        return render(request, 'host.html', {'v1':v1, 'v2':v2, 'v3':v3,'b_list':b_list})
    elif request.method == 'POST':
        h = request.POST.get('hostname')
        i = request.POST.get('ip')
        p = request.POST.get('port')
        b = request.POST.get('b_id')
        models.Host.objects.create(hostname=h,
                                   ip=i,
                                   port=p,
                                   b_id=b,)
        return redirect('/host')

host.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <h1>对象</h1>
    <table border="1" style="border-collapse: collapse;">
        <thead>
            <tr>
                <th>主机名</th>
                <th>IP</th>
                <th>端口</th>
                <th>业务线名称</th>
            </tr>
        </thead>
        <tbody>
            {% for row in v1 %}
                <tr hid="{{ row.nid }}" bid="{{ row.b_id }}">
                    <td>{{ row.hostname }}</td>
                    <td>{{ row.ip }}</td>
                    <td>{{ row.port }}</td>
{#                    查关联表的其他字段caption,这里b是关联表的对象名#}
                    <td>{{ row.b.caption }}</td>
                </tr>
            {% endfor %}
        </tbody>

    </table>
    <h1>字典</h1>
    <table border="1" style="border-collapse: collapse;">
        <thead>
            <tr>
                <th>主机名</th>
                <th>业务线名称</th>
            </tr>
        </thead>
        <tbody>
            {% for row in v2 %}
                <tr hid="{{ row.nid }}" bid="{{ row.b_id }}">
                    <td>{{ row.hostname }}</td>
{#                    查关联表的其他字段caption,这里b是关联表的对象名#}
                    <td>{{ row.b__caption }}</td>
                </tr>
            {% endfor %}
        </tbody>

    </table>
    <h1>元组</h1>
    <table border="1" style="border-collapse: collapse;">
        <thead>
            <tr>
                <th>主机名</th>
                <th>业务线名称</th>
            </tr>
        </thead>
        <tbody>
            {% for row in v3 %}
                <tr hid="{{ row.0 }}" bid="{{ row.2 }}">
                    <td>{{ row.1 }}</td>
                    <td>{{ row.3 }}</td>

{#                    查关联表的其他字段caption,这里b是关联表的对象名#}
                    <td>{{ row.b.caption }}</td>
                </tr>
            {% endfor %}
        </tbody>

    </table>
</body>
</html>

2.模板语言显示序号用forloop,只有for循环里有。

 <tbody>
                 {% for row in v1 %}
                     <tr hid="{{ row.nid }}" bid="{{ row.b_id }}">
                         <td>{{ forloop.counter  }}</td>
               {#在for循环里才有forloop,forloop.counter自动加序号,从1开始#}
               {#forloop.counter0自动加序号,从0开始#}
               {#forloop.revcounter自动加序号,倒数#}
                         <td>{{ row.hostname }}</td>
                         <td>{{ row.ip }}</td>
                         <td>{{ row.port }}</td>
                         <td>{{ row.b.caption }}</td>
                     </tr>
                 {% endfor %}
         </tbody>

3.后端接收前端多选框、Ctrl多选的数据用method.getlist()

views.py

     elif request.method == "POST":
         app_name = request.POST.get('app_name')
         host_list = request.POST.getlist('host_list')#当前端是多选传过来的数据时,用getlist接收,生成列表
         print(app_name,host_list)

         obj = models.Application.objects.create(name=app_name)
         obj.r.add(*host_list)  #多对多跨表添加数据:*列表名

         return redirect('/app')

html多选

             <div class="group">
                 <select id="host_list" name="host_list" multiple>
                  #multiple是多选的意思
                     {% for op in host_list %}
                         <option value="{{ op.nid }}">{{ op.hostname }}</option>
                     {% endfor %}
                 </select>
             </div>    

4.编辑框的实现和ajax提交表单数据
d20-host.html

{#    下边是编辑框展示#}
    <div class="shade hide"></div>
    <div class="add-modal hide">
        <form id="add_form" method="POST" action="/host">
            <div class="group">
                <input id="host" type="text" placeholder="主机名" name="hostname" />
            </div>

            <div class="group">
                <input id="ip" type="text" placeholder="IP" name="ip" />
            </div>

            <div class="group">
                <input id="port" type="text" placeholder="端口" name="port" />
            </div>

            <div class="group">
                <select id="sel" name="b_id">
                    {% for op in b_list %}
{#                        生成下拉框#}
                    <option value="{{ op.id }}">{{ op.caption }}</option>
                    {% endfor %}
                </select>
            </div>

            <input type="submit" value="提交" />
            <a id="ajax_submit" >悄悄提交</a>
            <input id="cancel" type="button" value="取消" />
            <span id="erro_msg" style="color: red"></span>
                 提交失败,错误信息显示
        </form>

$('#ajax_submit').click(function(){
                $.ajax({
                    url: "/test_ajax",
                    type: 'POST',
                    //data: {'hostname': $('#host').val(), 'ip': $('#ip').val(), 'port': $('#port').val(), 'b_id': $('#sel').val()},
                    data: $('#add_form').serialize(), //serialize()是ajax方法,它能自动找到表单中的name和value数据并提交给后台,跟上边的效果相同
                    success: function(data){
                        var obj = JSON.parse(data);
                        if(obj.status){
                            location.reload();//提交成功自动刷新页面
                        }else{
                            $('#erro_msg').text(obj.error);//提交失败显示错误信息
                        }
                    }
                })
            });

views.py
后台数据接收

     elif request.method == "POST":

         h = request.POST.get('hostname')
         i = request.POST.get('ip')
         p = request.POST.get('port')
         b = request.POST.get('b_id')
         models.Host.objects.create(hostname=h,
                                                ip=i,
                                                port=p,
                                                b_id=b,
                                                )
         return redirect('/host')        

5.初识ajax,ajax发送普通数据与发送列表

重要:在html中写ajax

ajax操作要点:
1. <form id='ajax_text'action='/yy.html' method='post'>  #id='ajax_text'方便ajax获取数据;action='/yy.html'点击取消后
                                                         跳转到的页面;

2.    <input  id="ajax_submit" type="button" value="提交" /> #id="ajax_submit" :方便ajax进行绑定
   </form>
3.       <span id="error_msg" style="color: white"></span>  #显示提示信息

4.            $('#ajax_submit').click(function(){
                $.ajax({
                    url: "/test_ajax",
                    type: 'POST',
                    dataType:'JSON',  #自动转换json数据
                    traditional:true,
                    data: $('#add_form').serialize(),//ajax用serialize()提交表单数据,serialize()能自动获取表单中的
                                                       name和value并提交给后台
                    success: function(data){
                        if(data.status){
                            location.reload();  //自动刷新页面
                        }else{
                            $('#error_msg').text(data.error); //在模态对话框里显示错误信息
                        }
                    }
                })
            });
    

ajax格式:

 Ajax格式:在html的script中
 A.发送数据格式为普通数据data: {'k1': 123,'k2': "root"}, $.ajax({ url: '/host', //数据提交的url type: "POST", //数据提交方式,除了post还有get data: {'k1': 123,'k2': "root"}, //要给后端发送提交的数据,data只能是字典
dataType:'JSON', //写上这句,就不用再在下边做字符串与字典转换了,ajax内部自动转换
success: function(data){ //success里写函数 // data是服务器端返回的字符串 var obj = JSON.parse(data); //有了上边的dataType:'JSON',这里就不用做字符串转换成字典了 } })  B.发送数据格式为列表:data: {'user': 123,'host_list': [1,2,3,4]},
      $.ajax({
       url: '/ajax_add_app',
       data: {'user': 123,'host_list': [1,2,3,4]},或
       data: $('#add_form').serialize(), //这里serialize()会找到id=add_form的表单,(这里id必须为表单<form>的id)并且自动获取form表单中的name和value,
                              因此不用再写成data: {'user': 123,'host_list': [1,2,3,4]}这样了
     type: "POST",
     dataType: 'JSON',// 内部 自动将字符串转换为字典
       traditional: true, //要发送列表必须要加traditional: true参数,否则发送为空
success: function(obj){ //请求成功,要发送的数据
   console.log(obj);
       },
       error: function () { //请求失败,要发送的数据
}
})
   建议:永远让服务器端返回一个字典 15 16 return HttpResponse(json.dumps(字典))

后端views.py,后端一定要永远给前端返回一个字典,而且return一定要返回HttpResponse

 def test_ajax(request):
     ret = {'status': True, 'error': None, 'data': None}  #初始化一个字典
     try:      #获取ajax请求
         h = request.POST.get('hostname')
         i = request.POST.get('ip')
         p = request.POST.get('port')
         b = request.POST.get('b_id')
         if h and len(h) > 5:   #判断用户名的长度是否大于5,如果小于5则返回error值
             models.Host.objects.create(hostname=h,
                                            ip=i,
                                            port=p,
                                            b_id=b)
         else:
             ret['status'] = False
             ret['error'] = "太短了"
     except Exception as e:
         ret['status'] = False
         ret['error'] = '请求错误'
     return HttpResponse(json.dumps(ret))  #这里只能用HttpResponse,而且HttpResponse里边只能写字符串,所以往前端传输字典的时候要将字典序列化成字符串

前端html,在js里,通过var obj = JSON.parse(data); 将后端发过来的字符串转换为字典

 <div class="add-modal hide">
         <form id="add_form" method="POST" action="/host">
             <div class="group">
                 <input id="host" type="text" placeholder="主机名" name="hostname" />
             </div>
             <div class="group">
                 <input id="ip" type="text" placeholder="IP" name="ip" />
             </div>

             <div class="group">
                 <input id="port" type="text" placeholder="端口" name="port" />
             </div>

             <div class="group">
                 <select id="sel" name="b_id">
                     {% for op in b_list %}
                     <option value="{{ op.id }}">{{ op.caption }}</option>
                     {% endfor %}
                 </select>
             </div>

             <input type="submit" value="提交" />
             <a id="ajax_submit" >悄悄提交</a>
             <input id="cancel" type="button" value="取消" />
             <span id="erro_msg" style="color: red"></span>
             {#  span显示错误信息#}
         </form>

  <script>
 $('#ajax_submit').click(function(){
                 $.ajax({
                     url: "/test_ajax",
                     type: 'POST',
                     //data: {'hostname': $('#host').val(), 'ip': $('#ip').val(), 'port': $('#port').val(), 'b_id': $('#sel').val()},
                     data: $('#add_form').serialize(),//ajax用serialize()提交表单数据,serialize()能自动获取表单中的name和value并提交给后台,效果跟上边这句一样
                     success: function(data){
                         var obj = JSON.parse(data);   //js将字符串转换为字典
                         var dd = JSON.stringify(obj) //js将字典转换为字符串
                         if(obj.status){
                             location.reload();  //自动刷新页面
                         }else{
                             $('#erro_msg').text(obj.error); //在模态对话框里显示错误信息
                         }
                     }
                 })
             });
  </script>

6.多对多创建关系表   有2种方式
   方式一:自定义关系表。推荐用这种方式创建

            class Host(models.Model):
                nid = models.AutoField(primary_key=True)
                hostname = models.CharField(max_length=32,db_index=True)
                ip = models.GenericIPAddressField(protocol="ipv4",db_index=True)
                port = models.IntegerField()
                b = models.ForeignKey(to="Business", to_field='id')

            class Application(models.Model):
                name = models.CharField(max_length=32)

            class HostToApp(models.Model):#生成hosttoapp关联表,该表有三列,分别是id、hobj_nid、aobj_id,还可以自定义增加其他列名
                hobj = models.ForeignKey(to='Host',to_field='nid')
                aobj = models.ForeignKey(to='Application',to_field='id')

在往方式一创建的关系表hosttoapp表中插入数据的时候,直接用普通的orm语句即可:

HostToApp.objects.create(hobj_id=1,aobj_id=2)

方式二:自动创建关系表。这种方式通过两个类可以创建三张表

            class Host(models.Model):
                nid = models.AutoField(primary_key=True)
                hostname = models.CharField(max_length=32,db_index=True)
                ip = models.GenericIPAddressField(protocol="ipv4",db_index=True)
                port = models.IntegerField()
                b = models.ForeignKey(to="Business", to_field='id')

            class Application(models.Model):
                name = models.CharField(max_length=32)
                r = models.ManyToManyField("Host") #这里用ManyToManyField关键字,可以自动创建第三张表----即host与application表之间的关联表,表名是:app01_application_r;
 其中app01是项目名,该表中含有三列,分别是:id、application_id、host_id,但该表不能自定义增加其他列

通过方式二创建的关系表app01_application_r表,是无法直接向该表插入数据的。要操作数据必须用以下方式:


            obj = Application.objects.get(id=1) #获取application表application_id=1所有数据的对象
        查 obj.name #获取application_id=1对应所有数据的应用名 # 第三张表(关系表)操作
        增
        添加数据操作,有三种方式: 1、obj.r.add(1) 添加数据。这里的1是另一列host_id的数值,即在application表中添加application_id=1,host_id=1 obj.r.add(2) 2、obj.r.add(2,3,4) 添加数据。这里的2,3,4是另一列host_id的数值,即在application表中添加application_id=1,host_id=2;application_id=1,host_id=3;
application_id=1,host_id=4的数值 3、obj.r.add(*[1,2,3,4]) 添加数据。这里的1,2,3,4是另一列host_id 的数值,即在application表中添加application_id=1,host_id=1;application_id=1,host_id=2;
application_id=1,host_id=3;application_id=1,host_id=4的数值
  obj.r.remove(1)  删除数据 obj.r.remove(2,4) obj.r.remove(*[1,2,3]) obj.r.clear()  清除数据。清除application表中application_id=1所对应的所有host_id的值   obj.r.set([3,5,7]) 修改数据。列表不加*,将application表中application_id=1所对应的所有host_id的值修改为3,5,7,即表中application_id=1所对应的所有host_id的值只剩下3,5,7 # 通过关系表app01_application_r查询host表
       obj = Application.objects.get(id=1)  ss = obj.r.all() 这里ss是querySet,这个querySet里存放的是application_id=1包括的所有host_id对应host表的一行行数据对象,也就是说ss就是host表中相应host_id所在行的数据对象集合,
 要获取每个host行对象的详细信息,要用for循环
for h in ss:
                   print(h.hostname,h.id,h.port)

7.多对多增加、编辑示例,用ajax,详见d20-12

views.py

from django.shortcuts import render,HttpResponse,redirect
from app01 import models
import json
# Create your views here.

def business(request):
    v1 = models.Business.objects.all()
    # QuerySet
    # [obj(id,caption,code),obj(id,caption,code),obj(id,caption,code) ]

    v2 = models.Business.objects.all().values('id','caption')
    # QuerySet
    # [{'id':1,'caption': '����'},{'id':1,'caption': '����'},...]

    v3 = models.Business.objects.all().values_list('id','caption')
    # QuerySet
    # [(1������),(2,����)]
    return render(request, 'business.html', {'v1': v1,'v2': v2, 'v3': v3})

# def host(request):
#     v1 = models.Host.objects.filter(nid__gt=0)
#     # QuerySet [hostobj(ip.host,����һ������(..)),]
#     # for row in v1:
#     #     print(row.nid,row.hostname,row.ip,row.port,row.b_id,row.b.caption,row.b.code,row.b.id,sep='\t')
#     #     print(row.b.fk.name)
#     # return HttpResponse("Host")
#     v2 = models.Host.objects.filter(nid__gt=0).values('nid','hostname','b_id','b__caption')
#     # QuerySet: [ {} ]
#     # print(v2)
#     # for row in v2:
#     #     print(row['nid'],row['hostname'],row['b_id'],row['b__caption'])
#
#     v3 = models.Host.objects.filter(nid__gt=0).values_list('nid','hostname','b_id','b__caption')
#     # QuerySet: [ {} ]
#     # print(v2)
#     return render(request, 'host.html', {'v1': v1,'v2': v2,'v3': v3})

def host(request):
    if request.method == "GET":
        v1 = models.Host.objects.filter(nid__gt=0)
        v2 = models.Host.objects.filter(nid__gt=0).values('nid','hostname','b_id','b__caption')
        v3 = models.Host.objects.filter(nid__gt=0).values_list('nid','hostname','b_id','b__caption')

        b_list = models.Business.objects.all()

        return render(request, 'host.html', {'v1': v1,'v2': v2,'v3': v3,'b_list':b_list})

    elif request.method == "POST":

        h = request.POST.get('hostname')
        i = request.POST.get('ip')
        p = request.POST.get('port')
        b = request.POST.get('b_id')
        # models.Host.objects.create(hostname=h,
        #                            ip=i,
        #                            port=p,
        #                            b=models.Business.objects.get(id=b)
        #                            )
        models.Host.objects.create(hostname=h,
                                   ip=i,
                                   port=p,
                                   b_id=b
                                   )
        return redirect('/host')

def test_ajax(request):
    ret = {'status': True, 'error': None, 'data': None}  #初始化一个字典
    try:      #获取ajax请求
        h = request.POST.get('hostname')
        i = request.POST.get('ip')
        p = request.POST.get('port')
        b = request.POST.get('b_id')
        if h and len(h) > 5:   #判断用户名的长度是否大于5,如果小于5则返回error值
            models.Host.objects.create(hostname=h,
                                           ip=i,
                                           port=p,
                                           b_id=b)
        else:
            ret['status'] = False
            ret['error'] = "太短了"
    except Exception as e:
        ret['status'] = False
        ret['error'] = '请求错误'
    return HttpResponse(json.dumps(ret))  #HttpResponse里边只能写字符串,所以往前端传输字典的时候要将字典序列化成字符串

def app(request):
    if request.method == "GET":
        app_list = models.Application.objects.all()
        # for row in app_list:
        #     print(row.name,row.r.all())

        host_list = models.Host.objects.all()  #从host表取host数据,来生成host列表下拉框
        return render(request,'app.html',{"app_list": app_list,'host_list': host_list})
    elif request.method == "POST":
        app_name = request.POST.get('app_name')
        host_list = request.POST.getlist('host_list')#  后端获取列表数据用getlist
        print(app_name,host_list)

        obj = models.Application.objects.create(name=app_name)
        obj.r.add(*host_list)  #往关系表中添加数据,这里的host_list是列表,所以要加*号

        return redirect('/app')

def ajax_add_app(request):
    ret = {'status':True, 'error':None, 'data': None}

    app_name = request.POST.get('app_name')
    host_list = request.POST.getlist('host_list')
    obj = models.Application.objects.create(name=app_name)
    obj.r.add(*host_list)
    return HttpResponse(json.dumps(ret))

app.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title></title>
    <style>
        .host-tag{
            display: inline-block;
            padding: 3px;
            border: 1px solid red;
            background-color: palevioletred;
        }
        .hide{
            display: none;
        }
        .shade{
            position: fixed;
            top: 0;
            right: 0;
            left: 0;
            bottom: 0;
            background: black;
            opacity: 0.6;
            z-index: 100;
        }
        .add-modal,.edit-modal{
            position: fixed;
            height: 300px;
            width: 400px;
            top:100px;
            left: 50%;
            z-index: 101;
            border: 1px solid red;
            background: white;
            margin-left: -200px;
        }
    </style>
</head>
<body>

    <h1>应用列表</h1>
     <div>
        <input id="add_app" type="button" value="添加" />
    </div>
    <table border="1">
        <thead>
            <tr>
                <td>应用名称</td>
                <td>应用主机列表</td>
            </tr>
        </thead>
        <tbody>
            {% for app in app_list %}
                <tr aid="{{ app.id }}">
                    <td>{{ app.name }}</td>
                    <td>
                        {% for host in app.r.all %}
                            <span class="host-tag" hid="{{ host.nid }}"> {{ host.hostname }} </span>
                        {% endfor %}
                    </td>
                    <td>
                        <a class="edit">编辑</a>
                    </td>
                </tr>
            {% endfor %}
        </tbody>
    </table>

    <div class="shade hide"></div>
    <div class="add-modal hide">
        <form id="add_form" method="POST" action="/app">
            <div class="group">
                <input id="app_name" type="text" placeholder="应用名称" name="app_name" />
            </div>
            <div class="group">
                <select id="host_list" name="host_list" multiple>
                    multiple是按住Ctrl多选,这里做一个下拉框,来进行多选选择
                    {% for op in host_list %}
                        <option value="{{ op.nid }}">{{ op.hostname }}</option>
                        这里的op.nid就是每个hostname对应的id,提交的时候,就会将这些选中的op.nid以列表的形式提交给后端
                    {% endfor %}
                </select>
            </div>

            <input type="submit" value="提交" />
            <input id="add_submit_ajax" type="button" value="Ajax提交" />
        </form>

    </div>

    <div class="edit-modal hide">
        <form id="edit_form" method="POST" action="/host">
                <input type="text" name="nid" style="display:none" />
                <input type="text" placeholder="应用名称" name="app" />
                <select name="host_list" multiple>
                    multiple是按住Ctrl多选,这里做一个下拉框,来进行多选选择
                    {% for op in host_list %}
                        <option value="{{ op.nid }}">{{ op.hostname }}</option>
                    {% endfor %}
                </select>
            <a id="ajax_submit_edit" >确认编辑</a>
        </form>

    </div>

     <script src="/static/jquery-1.12.4.js"></script>
    <script>
        $(function(){

            $('#add_app').click(function(){
                $('.shade,.add-modal').removeClass('hide');
            });

            $('#cancel').click(function(){
                $('.shade,.add-modal').addClass('hide');
            });

            $('#add_submit_ajax').click(function(){
                $.ajax({
                    url: '/ajax_add_app',
                    // data: {'user': 123,'host_list': [1,2,3,4]},
                    data: $('#add_form').serialize(),
                    type: "POST",
                    dataType: 'JSON', // 内部
                    traditional: true,
                    success: function(obj){
                        console.log(obj);
                    },
                    error: function () {

                    }

                })
            });

            $('.edit').click(function(){

                $('.edit-modal,.shade').removeClass('hide');

                var hid_list = [];
                $(this).parent().prev().children().each(function(){
                    var hid = $(this).attr('hid');
                    hid_list.push(hid)
                });

                $('#edit_form').find('select').val(hid_list);
                // 如果发送到后台
                //
                /*
                obj = models.Application.objects.get(id=ai)
                obj.name = "新Name"
                obj.save()
                obj.r.set([1,2,3,4])
                */

            })

        })

    </script>
</body>
</html>