Django-ORM

新建数据库，在models下

创建表的时候需要用到的命令

python manage.py makemigrations

python manage.py migrate

from django.db import models

# Create your models here.
class UserInfo(models.Model):
    username = models.CharField(max_length=32)
    password = models.CharField(max_length=64)

class host(models.Model):
    hostname = models.CharField(max_length=30)
    ip = models.CharField(max_length=30)
    sn = models.CharField(max_length=30)
    depart = models.ForeignKey(to="UserInfo",to_field="id")   #一对多关联UserInfo表，前提UserInfo表里要有数据

class UserGroup(models.Model):
    Groupname = models.CharField(max_length=30)
    m = models.ManyToManyField("UserInfo")   #多对多的命令这个m代表UserGroup组

#自己创建关联
# class U2G(models.Model):
#      ui = models.ForeignKey("UserInfo")
#      ug = models.ForeignKey('UserGroup')

ORM操作

####表内创建数据
    # UserInfo.objects.create(username='alex',password='123456') 插入用户名的
    # host.objects.create(hostname='SL010A-TMSDB2',ip='192.168.1.6',sn='DBVF4D',depart_id='2')插入主机信息

    ######正向跨表#####
    ##########1、#获取主机的所有信息
    # q = host.objects.all()
    # for i in q:  #循环得出     i.depart.username得出关联表的管理者
    #     print(i.hostname,i.ip,i.sn,ai.depart_id,i.depart.id,i.depart.username,i.depart.password)

    ##########2、获取用户所有信息，部门名称values 取个别字段  里面是字典   取另一张表需要双下划线
    # q = host.objects.values('hostname','ip','sn','depart_id','depart__username','depart__password')
    # for row in q:
    #     # print(row) 取所有
    #     print(row['hostname'],row['depart__username'])   #只取主机名和应用管理人

    ##########3、获取用户所有信息，部门名称values_list 取个别字段  里面是元组
    # q = host.objects.values_list('hostname','ip','sn','depart_id','depart__username','depart__password')
    # for row in q:
    # #    # print(row)   取所有
    # #    print(row[0],row[4])   取主机名和应用管理人

    #########反向跨表
    # u = UserInfo.objects.all()
    # for i in u:
    #                              #反向跨表
    #     #print(i.username,i.host_set.values('hostname','ip','sn','depart_id','depart__username','depart__password'))
    #     p = i.username,i.host_set.values('hostname','ip','sn','depart_id','depart__username','depart__password')
    #     if i.username == 'lisa':
    #         l = i.host_set.values('hostname','ip','sn','depart_id','depart__username','depart__password')
    #         print (l[0]['sn'])
    #         print(l[1]['ip'])

    #####正向字典显示然后输出
    # v = host.objects.values('hostname','ip','sn','depart_id','depart__username')
    # for i in v:
    #     if i['depart__username'] == 'lisa':
    #         print(i)
    #######利用表名直接全取###
    # v = UserInfo.objects.values('username','host')
    # for i in v:
        #print(i)
    ######以下是通过判断姓名下面所关联的所有host
        # if i['username'] == 'lisa':
        #     print(i)
        ###结果如下[24/May/2017 09:56:48] "GET /crdb/ HTTP/1.1" 200 16
            #{'host': 4, 'username': 'lisa'}
            #{'host': 9, 'username': 'lisa'}
    ########################################################

    #####反向取结果3########
    # v = UserInfo.objects.values('username', 'host__hostname','host__ip','host__sn')
    # for row in v:
    #     print(row)
    ##########结果如下####
    #'host__ip': '192.168.1.2', 'host__sn': 'ASCDES', 'username': 'huige', 'host__hostname': 'TMSA1'}
    #{'host__ip': '192.168.1.5', 'host__sn': 'SWSXDE', 'username': 'lisa', 'host__hostname': 'TMSAB2'}
    #{'host__ip': '192.168.1.6', 'host__sn': 'DBVF4D', 'username': 'lisa', 'host__hostname': 'SL010A-TMSDB2'}
    #{'host__ip': '192.168.1.4', 'host__sn': 'SDCVRD', 'username': 'wupeiqi', 'host__hostname': 'TMSDB1'}
    #{'host__ip': '192.168.1.3', 'host__sn': '1WSXGV', 'username': 'alex', 'host__hostname': 'TMSB2'}
    #######
    ##通过以上如果只取用户为Lisa的呢###如下##
    # v = UserInfo.objects.values('username', 'host__hostname', 'host__ip', 'host__sn')
    # for row in v:
    #     if row['username'] == 'lisa':
    #         print(row)
    #####结果如下####
    #{'username': 'lisa', 'host__sn': 'SWSXDE', 'host__ip': '192.168.1.5', 'host__hostname': 'TMSAB2'}
    #{'username': 'lisa', 'host__sn': 'DBVF4D', 'host__ip': '192.168.1.6', 'host__hostname': 'SL010A-TMSDB2'}
    #################

    #反向的set语句
    # v = UserInfo.objects.all()
    # for i in v:
    #     print(i.username, i.host_set.values('ip', 'hostname'))
    ######输出如下####
    # huige < QuerySet[{'hostname': 'TMSA1', 'ip': '192.168.1.2'}] >
    # lisa < QuerySet[{'hostname': 'TMSAB2', 'ip': '192.168.1.5'}, {'hostname': 'SL010A-TMSDB2', 'ip': '192.168.1.6'}] >
    # wupeiqi < QuerySet[{'hostname': 'TMSDB1', 'ip': '192.168.1.4'}] >
    # alex < QuerySet[{'hostname': 'TMSB2', 'ip': '192.168.1.3'}] >
    ###########
    #################通过以上总结正向跨表直接用forinker字段就OK，反向用表明下划线set或着直接用表名双下划线字段名#######

    ###多对多自己写第三张表对列无限制
    # 插入关系用户和组
    # U2G.objects.create(ui_id=1,ug_id=2)
    # U2G.objects.create(ui_id=2,ug_id=2)
    # U2G.objects.create(ui_id=5,ug_id=3)
    ###############
    ###自己写的查找###
    # v = U2G.objects.all()
    # for row in v:
    #     print(row.ui.username,row.ug.Groupname)
    ####输出如下：
    # huige IV技术部
    # lisa IV技术部
    # wupeiqi 网运事业部

    #多对多通过ManyToManyFild创建第三张表，列有限制
    #增加关联使用add
    # obj = UserGroup.objects.filter(id=2).first() #first意思是查找到的意思 filter(id=1) 代表用户组的id
    # obj.m.add(5)        #关联用户的ID，增加
    ####批量关联
    # obj = UserGroup.objects.filter(id=3).first()
    # obj.m.add(1,2)
    ##也能使用obj.m.add(1,2,3,4)   一下关联4个
    ###删除关联remove
    # obj = UserGroup.objects.filter(id=2).first()  # first意思是查找到的意思 filter(id=1) 代表用户组的id
    # obj.m.remove(5)  #删除组ID为2 用户ID为5的
    ###批量删除1
    # obj = UserGroup.objects.filter(id=1).first()
    # obj.m.remove(1,2)
    ###批量删除2
    # obj = UserGroup.objects.filter(id=1).first()
    # obj.m.remove(*[5,])
    ####清空 把组ID==*的用户清空
    # obj = UserGroup.objects.filter(id=2).first()  #把组ID=2 的所有用户与ID=2 的组解除关联
    # obj.m.clear()

    ##多对多表的更新set
    # obj = UserGroup.objects.filter(id=2).first()
    # obj.m.set([3,1,2])    #这个set是清空所有关联 然后创建新的关联

    #多对多查询
    # obj = UserGroup.objects.filter(id=1).first()
    # # v = obj.m.all()
    # # print(v)
    # q = obj.m.filter(id__gt = 2)    #filter过滤
    # print(q)