django ORM

1、models.py中创建类和字段

from django.db import models

# Create your models here.


class Business(models.Model):
    caption = models.CharField(max_length=32)
    code = models.CharField(max_length=32,default='SA')#default='SA' 默认是SA
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')

外键关联的时候如果没写和另一个表的哪个字段关联的话，默认是和主键关联。

2、python manage.py makemigrations

　　python manage.py migrate

settings.py 需要注册APP

如果表中已经存在数据，新增字段的时候加上default='SA'或者null=True

views.py

from django.shortcuts import render
from app01 import models

# Create your views here.
def business(request):
    v1 = models.Business.objects.all()
    #QuerySet
    v2 = models.Business.objects.all().values('id','caption')
    v3 = models.Business.objects.all().values_list('id','caption')
    return render(request,'business.html',{'v1':v1,'v2':v2,'v3':v3})
def host(request):
    v = models.Host.objects.filter(nid__gt=0)
    print(v)
    return render(request,'host.html',{'v':v})

单表查询的三种方式

# QuerySet ,内部元素都是对象

　　　　v1 = models.Business.objects.all()

# QuerySet ,内部元素都是字典

　　　　v2 = models.Business.objects.all().values('id','caption')

# QuerySet ,内部元素都是元组

　　　　v3 = models.Business.objects.all().values_list('id','caption')

　　获取到的一个对象，如果不存在就报错
　　　　models.Business.objects.get(id=1)
　　对象或者None = models.Business.objects.filter(id=1).first()、

一对多跨表操作

def host(request):
    if request.method == 'GET':
        v = models.Host.objects.filter(nid__gt=0)
        v1 = models.Host.objects.filter(nid__gt=0).values('hostname','ip','port','b_id','b__caption')
        v2 = models.Host.objects.filter(nid__gt=0).values_list('hostname','ip','port','b_id','b__caption')#跨表时用__
        b = models.Business.objects.all()
        print(b)
        return render(request,'host.html',{'v':v,'v1':v1,'v2':v2,'b':b})
    elif request.method == 'POST':
        h = request.POST.get('hostname')
        ip = request.POST.get('ip')
        p = request.POST.get('port')
        b = request.POST.get('b')
        models.Host.objects.create(hostname=h,ip=ip,port=p,b_id=b)
        return redirect('/host')

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <table border="1">
        <thead>
            <tr>
                <th>主机名</th>
                <th>ip</th>
                <th>端口</th>
                <th>业务线</th>
                <th>编号</th>
            </tr>
        </thead>
        <tbody>
            {% for row in v %}
                <tr nid="{{ row.nid }} " pid="{{ row.b_id }}">
                    <td>{{ row.hostname }}</td>
                    <td>{{ row.ip }}</td>
                    <td>{{ row.port }}</td>
                    <td>{{ row.b_id }}</td>
                    <td>{{ row.b.caption }}</td>
                </tr>

            {% endfor %}

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

PS：在host表中，外键关联business表，在创建完host表的时候，会出现b_id字段，就是business的id。但是也能通过b这个字段来查询business表的属性，此时row.b就是business对象，business表的一行数据,row.b.caption就能查询到business的字段。

在business表中，host_set能反查

from django.db import models

# Create your models here.
class UserType(models.Model):
    name = models.CharField(max_length=32,unique=True)

class User(models.Model):
    name = models.CharField(max_length=64)
    pwd = models.CharField(max_length=64)
    type = models.ForeignKey(to='UserType',to_field='id',related_name='b')

正向查询

v= models.User.objects.all()

方式一：

for item in v:

　　item.name

　　item.pwd

　　item.type.name

方式二：

models.User.objects.all().values('name','type__name')

反向查询

v = models.UserType.objects.all()

方式一：

for item in v:

　　item.name

　　item.id

　　item.user_set.all() --item.b.all()

方式二：

models.UserType.objects.all().values('name','user__pwd')

ajax

        $(function () {
            $('#ajax_add').click(function () {
                $.ajax({
                    url:'/test_ajax/',
                    type:'POST',
                    data:{'h':$('#host').val(),'ip':$('#ip').val(),'port':$('#port').val()},
                    success:function (data) {#data是服务器端返回的字符串
                        if(data=='ok'){
                            location.reload()
                        }else{
                            alert(data);
                        }
                    }
                })
            })

        })

如果发送的是一个列表，加上traditional=True

success后面的函数function只有在post数据后得到返回值才会执行

让服务器端返回一个字典

return HttpResponse

views.py

def test_ajax(requset):
    import json
    ret = {'status':True,'error':None,'data':None}

    try:
        h = requset.POST.get('h')
        ip = requset.POST.get('ip')
        port = requset.POST.get('port')
        b_id = requset.POST.get('b_id')
        if h and len(h) > 5:
            models.Host.objects.create(hostname=h,ip=ip,port=port,b_id=b_id)
        else:
            ret['status'] = False
            ret['error'] = '太短了'
    except Exception as e:
        ret['status'] = False
        ret['error'] = '请求错误'
    return HttpResponse(json.dumps(ret))

host.html

$.ajax({
                    url:'/test_ajax/',
                    type:'POST',
                    data:{'h':$('#host').val(),'ip':$('#ip').val(),'port':$('#port').val(),'b_id':$('#b_id').val()},
                    success:function (data) {
                        var obj = JSON.parse(data);
                        if (obj.status){
                            location.reload();
                        }else {
                            $('#msg').text(obj.error);
                        }
                        console.log(data)

                    }
                })
            })

js中字符串-》字典 JSON.parse

页面重新加载　　location.reload()

多对多

方式一：自定义创建

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 HostToApplication(models.Model):
    hobj = models.ForeignKey('Host',to_field='nid')
    aobj = models.ForeignKey('Application',to_field='id')

方式二：django自动创建第三张表，只能有3列

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')

正向增加：通过app加主机

　　app_obj = models.Application.objects.get(id=1)

　　host_obj = models.Host.objects.all()

　　app_obj.r.add(*host_obj)

　　host_obj.r.add(*[1,2,3])

正向查找：通过app查主机

　　app_obj = models.Application.objects.get(id=1)

　　for host in app_obj.r.all():

　　　　host.ip

反向增加：通过主机加app 主机table中有隐藏字段application_set

　　host_obj = models.Host.objects.get(ip='1.1.1.1')

　　application_obj = models.Application.objects.filter(id__lt=2)

　　host_obj.application_set.add(*application_obj)

反向查询：

　　host_obj = models.Host.objects.get(ip='1.1.1.1')

　　app_obj = host_obj.application_set.all()

　　for app in app_obj:

　　　　print(app.name)

obj.remove(*[1,2,3])

obj.r.clear()

obj.r.set([3,5,7])

所有相关的主机对象‘列表’

obj.r.all()

*外键用select_related()

*多对多 prefetch_related()

更多多对多操作请看

http://www.mamicode.com/info-detail-1292278.html