数据库一对多，多对多关系

一对多

#ForeignKey做约束

class Depart(models.Model):
    title = models.CharField(verbose_name="标题", max_length=32)


class UserInfo(models.Model):
    name = models.CharField(verbose_name="姓名", max_length=32)
        #depart_id 外键约束， db_constraint=False是否有约束，业务代码要加上约束
    depart = models.ForeignKey(verbose_name="部门", to="Depart", on_delete=models.CASCADE, db_constraint=False)
    
    
# 反向操作用
    related_name=None,       【反向全部都改变】
    related_query_name=None  【查询条件】    

#查询姓名，部门
obj = models.UserInfo.objects.first(name="楚留香").first()

obj.id
obj.name
obj.depart_id
obj.depart.title


#查询 销售部 所有人姓名  进行了跨表查询 Userinfo + Depart
queryset =models. UserInfo.objects.first(depart__title="销售部")
for obj in queryset:
    print(obj.name)

    
#或者，反向管理
depart_object = models.Depart.objects.filter(title="销售部").first()
depart_object.id
depart_object.title
depart_object.userinfo_set.all()

queryset = depart_object.userinfo_set.all()
for obj in queryset:
    obj.name
    obj.depart_id
        
# depart_object = models.Depart.objects.filter(title="销售部", userinfo__name='陆小凤')

多个字段

models.py

from django.db import models


class Info(models.Model):
    name = models.CharField(verbose_name="姓名", max_length=32)
    extra = models.FileField(verbose_name="附件", upload_to="upload")


class Info2(models.Model):
    name = models.CharField(verbose_name="姓名", max_length=32)
    extra = models.CharField(verbose_name="附件", max_length=64)


class Depart(models.Model):
    title = models.CharField(verbose_name="标题", max_length=32)


class UserInfo(models.Model):
    name = models.CharField(verbose_name="姓名", max_length=32)

    depart = models.ForeignKey(verbose_name="部门", to="Depart", on_delete=models.CASCADE)


class Admin(models.Model):
    """员工表"""
    name = models.CharField(verbose_name="姓名", max_length=32)


class Device(models.Model):
    title = models.CharField(verbose_name="名称", max_length=32)
    price = models.PositiveIntegerField(verbose_name="价格")
    #如果是Admin关联buy, use就会重复，无法分辨，所以要添加related_name字段
    buy = models.ForeignKey(verbose_name="采购者", to="Admin", on_delete=models.CASCADE, related_name="x1")
    use = models.ForeignKey(verbose_name="使用者", to="Admin", on_delete=models.CASCADE, related_name="x2")

#所以可以这么关联
obj = models.Admin.objects.filter(id=1).first()
obj.id
obj.name
obj.x1.all()
obj.x2.all()

一对一

obj = models.Assets.objects.create(v1="电脑")
models.Others.objects.create(assets=obj, v2="1000")

obj = models.Assets.objects.all().first()
print(obj.id)
print(obj.v1)
print(obj.others.v2)
models.Assets.objects.filter(v1="xxx", others__v2=112)

多对多

比如男生女生之间约会，需要第三张表存储约会双方的数据

class Boy(models.Model):
    """男生表
    id name
    """
    name = models.CharField(verbose_name="姓名", max_length=32)


class Girl(models.Model):
    """女生表
    id  name
    """
    name = models.CharField(verbose_name="姓名", max_length=32)
    #自动创建第三张表
    xx = models.ManyToManyField(to="Boy")

class Boy(models.Model):
    """男生表
    id name
    """
    name = models.CharField(verbose_name="姓名", max_length=32)
    girls = models.ManyToManyField(to="Girl", through="Boy2Girl", through_fields=("boy", "girl"))


class Girl(models.Model):
    """女生表
    id  name
    """
    name = models.CharField(verbose_name="姓名", max_length=32)


class Boy2Girl(models.Model):
    """
    关系表
    """
    boy = models.ForeignKey(verbose_name="男生", to="Boy", on_delete=models.CASCADE)
    girl = models.ForeignKey(verbose_name="女生", to="Girl", on_delete=models.CASCADE)

多对多操作方式

1

def demo(request):
    models.Boy.objects.create(name="男1")
    models.Boy.objects.create(name="男2")
    models.Boy.objects.create(name="男3")

    models.Girl.objects.create(name="女1")
    models.Girl.objects.create(name="女2")
    models.Girl.objects.create(name="女3")
    models.Girl.objects.create(name="女4")
    
    return HttpResponse("OK")

    obj = models.Girl.objects.filter(name='女1').first()
    obj.xx.set([1, 3])
    obj.xx.add(2)
    boy_list = obj.xx.all()
    print(boy_list)

反向操作

obj = models.Boy.objects.filter(name='男3').first()
girl_list = obj.girl_set.all()
print(girl_list)
obj.girl_set.add(3)
obj.girl_set.all()
res = obj.girl_set.all()
print(res)

 #这是在删除girl表的女三，也就是说girl_xx所有girl_id=3的列也全没了
 res = obj.girl_set.filter(id=3).delete()
 print(res)
    
#建议这么删除girl_xx表
obj.girl_set.remove(1)

2

#男1的约会对象
data_list = models.Boy2Girl.objects.filter(boy__name="男1")
for obj in data_list:
    print(obj.boy.name, obj.girl.name)
#女2的约会对象
data_list = models.Boy2Girl.objects.filter(girl__name="女2")
for obj in data_list:
    print(obj.girl.name, obj.boy.name)

或者

class Boy(models.Model):
    """男生表
    id name
    """
    name = models.CharField(verbose_name="姓名", max_length=32)
    #无法新建，只做查询
    girls = models.ManyToManyField(to="Girl", through="Boy2Girl", through_fields=("boy", "girl"))


class Girl(models.Model):
    """女生表
    id  name
    """
    name = models.CharField(verbose_name="姓名", max_length=32)


class Boy2Girl(models.Model):
    """
    关系表
    """
    boy = models.ForeignKey(verbose_name="男生", to="Boy", on_delete=models.CASCADE)
    girl = models.ForeignKey(verbose_name="女生", to="Girl", on_delete=models.CASCADE)

boy_object = models.Boy.objects.filter(name="男1").first()
res = boy_object.girls.all()
print(res)

girl_object = models.Girl.objects.filter(name="女1").first()
res = girl_object.boy_set.all()
print(res)

orm的操作

增删改查+其他

# 批量创建
    models.Boy.objects.bulk_create([
                models.Boy(name="admin"),
                models.Boy(name="root"),
                models.Boy(name="user"),
            ], 10)
    
#- 查询指定列   [对象,对象] only   defer是排除
queryset = models.Device.objects.all().only("title", "price")
for obj in queryset:
    # obj是对象
    print(obj.id, obj.title, obj.price, obj.buy_id)  

# 查询指定列   [字典,字典]
queryset = models.Boy.objects.all().values("id", "name")
for obj in queryset:
    print(obj)  # obj是字典
    
 #- 查询指定列   [元组,元组]
queryset = models.Boy.objects.all().values_list("id", "name")
print(queryset, type(queryset))


# - 主动连表  select_related
queryset = models.UserInfo.objects.all()
for item in queryset:
    print(item.id, item.name, item.depart_id, item.depart.title)

queryset = models.UserInfo.objects.all().select_related("depart")
for item in queryset:
    print(item.id, item.name, item.depart_id, item.depart.title)

# values_list
queryset = models.UserInfo.objects.all().values("id", "name", "depart__title")
print(queryset.query)

queryset = models.UserInfo.objects.filter(id=1, depart__title="销售")
print(queryset.query)



 #- 多次单表查询  prefetch_related
 # 100   1/2
# 去部门表    1/2  内存
queryset = models.UserInfo.objects.all().prefetch_related("depart")
for item in queryset:
    print(item.id, item.name, item.depart.title)

       

原生的sql

# - django.db.models.query.QuerySet
# 
# - django中如何执行原生SQL，所有ORM无法帮我们实现复杂SQL查询。

def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
    # 构造额外的查询条件或者映射，如：子查询

    # id name age
    res = Entry.objects.all()

    # select * from xxx where ...
    Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
    Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])

    # select id,name,age from Entry;
    Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
    Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])


def raw(self, raw_query, params=None, translations=None, using=None):
    # 执行原生SQL
    models.Entry.objects.raw('select * from userinfo')

    # 如果SQL是其他表时，必须将名字设置为当前Entry对象的主键列名   其他表 id
    # nid
    models.Entry.objects.raw('select id as nid from 其他表')

    # 为原生SQL设置参数
    models.Entry.objects.raw('select id as nid from userinfo where nid>%s', params=[12, ])

    name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
    Entry.objects.raw('SELECT * FROM some_other_table', translations=name_map)


from django.db import connection, connections

cursor = connection.cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
row = cursor.fetchone()
# fetchall()/fetchmany(..)   


#- 多数据库
mdoels.UserInfo.objects.all().using("default")
mdoels.UserInfo.objects.filter().using("default")