09 ORM 多表操作，创建表，添加记录

2022-08-20 14:33:40

1、数据库表关系

　　1.一对多

为什么需要，重复字段太多

一对多关系表

Book

    id  title   price   publish_id

    1   python  100         1

    2   php     200         2

    3   go      100         1

    4   java    300         1

Publish

    id  name        email       addr

    1   人民出版社   123@qq.com  北京

    2   南京出版社   456@qq.com  南京

#总结：一旦确定表关系是一对多：在多对应的表中创建关联字段,publish_id

   #查询python这本书的出版社的邮箱（子查询）

    select Publish.email from Publish where Publish.id = ()

    select Book.publish_id from Book where Book.title='python'

2.多对多

Book

    id  title   price   publish_id

    1   python  100         1

    2   php     200         2

    3   go      100         1

    4   java    300         1

Author

    id  name    age     addr

    1   alex    34      beijing

    2   jack    33      nanjing

Book2Author

    id  book_id author_id

    1       2       1

    2       2       2

    3       3       2

# 总结：一旦确定表关系是多对多：创建第三张表

    Book2Author

            id  book_id author_id

# alex出版过的书籍名称（子查询）

select Book.title from Book where Book.id in ()

select Book2Author.book_id where author_id = ()

select Author.id from Author where Author.name='alex'

　　3.一对一

Author

    id  name    age     ad_id(unique)   id  addr        gender  tel     gf_name

    1   alex    34          1           1   beijing     male    110     小花

    2   jack    33          2           2   nanjing     female  999     红娘

# 为了解耦

#方式1：

Author

    id  name    age     ad_id(unique)

    1   alex    34          1

    2   jack    33          2

AuthorDetail

    id  addr        gender  tel     gf_name

    1   beijing     male    110     小花

    2   nanjing     female  999     红娘

#方式2：

Author

    id  name    age

    1   alex    34

    2   jack    33

AuthorDetail

    id  addr        gender  tel     gf_name     author_id(unique)

    1   beijing     male    110     小花              1

    2   nanjing     female  999     红娘              2

#总结：一旦确定关系为一对一：在两张表中的任意一张表中建立关联字段+Unique

2、sql语句创建关联表

Publish

Book

Author

Book2Author

AuthorDetail

create table publish(

                    id int primary key auto_increament,

                    name varchar(20)

                    );

create table book(

                id int primary key auto_increament,

                title varchar(20),

                price decimal(8,2),

                pub_date date,

                publish_id int,

                foreign key(publish_id) references publish(id)

                );

create table authordetail(

                id int primary key auto_increament,

                tel varchar(20),

                );

create table author(

                id int primary key auto_increament,

                name varchar(20),

                age int,

                authordetail_id int unique,

                foreign key(authordetail_id) references authordetail(id)

                );

create table Book2Author(

                id int primary key auto_increament,

                book_id int,

                author_id int,

                foreign key(book_id) references book(id),

                foreign key(author_id) references author(id)

                );

3、ORM生成关联表模型

一对一

"""

1对1  author

create table author(

                id int primary key auto_increament,

                name varchar(20),

                age int,

                authordetail_id int unique,

                foreign key(authordetail_id) references authordetail(id)

                );

create table authordetail(

                id int primary key auto_increament,

                tel varchar(20),

                );

"""

# 作者表

class Author(models.Model):

    nid = models.AutoField(primary_key=True)

    name = models.CharField(max_length=32)

    age = models.IntegerField()

    # 1对1

    # authordeatil = models.OneToOneField(to=AuthorDetail, to_field=nid)        # 如果AuthorDetail表在后面定义也可以找到

    # authordeatil = models.OneToOneField(to="AuthorDetail", to_field="nid")      # 推荐使用字符串格式，    # django2.0 会报错，级联删除

    authordeatil = models.OneToOneField(to="AuthorDetail", to_field="nid", on_delete=models.CASCADE)     

# 作者详情表

class AuthorDetail(models.Model):

    nid = models.AutoField(primary_key=True)

    birthday = models.DateField()

    telephone = models.BigIntegerField()

    addr = models.CharField(max_length=32)

一对多、多对多

"""

一对多   Book -- Publish

create table publish(

                    id int primary key auto_increament,

                    name varchar(20)

                    );

create table book(

                id int primary key auto_increament,

                title varchar(20),

                price decimal(8,2),

                pub_date date,

                publish_id int,

                foreign key(publish_id) references publish(id)

                );

"""

# 出版社表

class Publish(models.Model):

    nid = models.AutoField(primary_key=True)

    name = models.CharField(max_length=32)

    city = models.CharField(max_length=32)

    email = models.EmailField()

# 书籍表

class Book(models.Model):

    nid = models.AutoField(primary_key=True)

    title = models.CharField(max_length=32)

    price = models.DecimalField(max_digits=5, decimal_places=2)

    publishDate = models.DateField()

    # 1对多关系

    # publish = models.ForeignKey(to=Publish, to_field='nid')  # django2.0 会报错，级联删除

    publish = models.ForeignKey(to=Publish, to_field='nid', on_delete=models.CASCADE)

    # 多对多

    authors = models.ManyToManyField(to="Author")  #  manytomany不会报错级联删除

    """

    多对多  Book----  Book2Author ---- Author

    create table Book2Author(

                    id int primary key auto_increament,

                    book_id int,

                    author_id int,

                    foreign key(book_id) references book(id),

                    foreign key(author_id) references author(id)

                    );

    """

多对多可以继续定义Book2Author，推荐使用ORM的 ManyToManyField

4、生成数据库表

级联删除错误

C:\PycharmProjects\ORM2>python manage.py makemigrations

Traceback (most recent call last):

  File "manage.py", line 15, in <module>

    execute_from_command_line(sys.argv)

  File "C:\PycharmProjects\ORM2\app01\models.py", line 72, in <module>

    class Author(models.Model):

  File "C:\PycharmProjects\ORM2\app01\models.py", line 79, in Author

    authordeatil = models.OneToOneField(to="AuthorDetail", to_field="nid")

TypeError: __init__() missing 1 required positional argument: 'on_delete'

# 级联删除字段  on_delete=models.CASCADE    一对多 一对一

publish = models.ForeignKey(to=Publish, to_field='nid', on_delete=models.CASCADE)

authordeatil = models.OneToOneField(to="AuthorDetail", to_field="nid", on_delete=models.CASCADE)

# authors = models.ManyToManyField(to="Author")  多对多 不会报错

python manage.py makemigrations

python manage.py migrate

注意事项：

表的名称myapp_modelName，是根据模型中的元数据自动生成的，也可以覆写为别的名称　　
id 字段是自动添加的
对于外键字段，Django 会在字段名上添加"_id" 来创建数据库中的列名
这个例子中的CREATE TABLE SQL 语句使用PostgreSQL 语法格式，要注意的是Django 会根据settings 中指定的数据库类型来使用相应的SQL 语句。
定义好模型之后，你需要告诉Django _使用_这些模型。你要做的就是修改配置文件中的INSTALL_APPSZ中设置，在其中添加models.py所在应用的名称。
外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL)，你可以赋给它空值 None 。

5、多表操作添加记录

url

from django.contrib import admin

from django.urls import path, re_path, include

urlpatterns = [

    path('admin/', admin.site.urls),

    re_path(r'^app01/', include(('app01.urls', 'app01')))

]

from django.urls import path, re_path, include

from app01 import views

urlpatterns = [

    re_path(r'^add/$', views.add, name='add')

]

　　1.单表

from django.shortcuts import render,HttpResponse

# Create your views here.

from app01.models import *

def add(request):

    # 1.单表添加记录

    ret1 = Publish.objects.create(

        name='人民出版社',

        city='beijing',

        email='123@qq.com',

    )

    ret2 = Publish.objects.create(

        name='南京出版社',

        city='nanjing',

        email='456@qq.com',

    )

    print(ret2)     # Publish object (2)

    return HttpResponse('OK')

　　2.一对多，一对一

　　绑定一对多的关系

　　方式1 方式2都有对象产生

　　（1）方式1

    # 方式1

    # 为book表绑定出版社

    book_obj = Book.objects.create(

        title='西游记',

        price=100,

        publishDate="2012-11-11",

        publish_id=1,

    )

    print(book_obj)                 # 西游记

    print(book_obj.title)           # 西游记

    print(book_obj.price)           #

    print(book_obj.publishDate)     # 2012-11-11

    print(book_obj.publish)         # Publish object (1)

    print(book_obj.publish_id)      #

　　　（2）方式2

    # 方式2

    # publish_obj = Publish.objects.filter(id=2).first()   # 没有id属性

    publish_obj = Publish.objects.filter(nid=2).first()   # 先查找publish_obj 对象

    book_obj = Book.objects.create(

        title='红楼梦',

        price=200,

        publishDate="2012-11-11",

        publish=publish_obj,

    )

    print(book_obj.title)

    print(book_obj.price)

    print(book_obj.publishDate)

    print(book_obj.publish)                # 与这本书关联的出版社对象

    print(book_obj.publish.email)         # 出版社对象,可以继续点方法

    print(book_obj.publish_id)

　　（3）查询

    # 查询红楼梦的出版社对应的邮箱

    book_obj = Book.objects.filter(title='红楼梦').first()

    print(book_obj.publish.email)       # 456@qq.com

　　（4）一对一

    author_obj = Author.objects.create(

        name='alex',

        age=22,

        authordetail_id=1,

    )

    author_obj = Author.objects.create(

        name='jack',

        age=23,

        authordetail_id=2,

    )

3、多对多

　　（1）绑定多对多关系

  # 3.绑定多对多的关系

      book_obj = Book.objects.create(

        title="大话设计模式",

        price=200,

        publishDate="2018-12-12",

        publish_id=1,

    )

    alex = Author.objects.get(name='alex')

    jack = Author.objects.get(name='jack')

    # 绑定多对多关系的API

    book_obj.authors.add(alex, jack)

    # book_obj.authors.add(1, 2, 3)  # eroor?

    book_obj.authors.add(*[1, 2, 3])

    # 推荐

　  id_li = [1,2,3]

    book_obj.authors.add(*id_li)

　　（2）解除多对多关系

    # 4.解除多对多关系

    book_obj = Book.objects.filter(nid=4).first()

    book_obj.authors.remove(2)

    book_obj.authors.remove(*[1,2])

    book_obj.authors.clear()

　　（3）多对多查询

    # 查询主键为4的书籍的所有作者的名字

    print(book_obj.authors.all())    # [obj1,obj2,...] querySet    # 与这本书关联的所有作者对象集合

    # <QuerySet [<Author: Author object (1)>]>

    ret = book_obj.authors.all().values('name')

    print(ret)   # <QuerySet [{'name': 'alex'}]>

4、bulk_create多对多，添加

data

{

    "alarm_info_id":797,

    "notifier_data":[

        {"id": 2191, "channel": ""},

        {"id": 2392,  "channel": ""}

        ]

}

实现

 def record_notifier_channel(self, request, *args, **kwargs):

        """记录告警人和告警渠道"""

        # 根据alarm_info_id和告警人id，create记录

        alarm_info_id = request.data.get('alarm_info_id')

        notifier_data = request.data.get('notifier_data')

        if not alarm_info_id:

            return self.render_to_json_response(status=1, msg='请输入alarm_info_id')

        # 取出数据

        alarm_notifier_info_list = []

        for notifier in notifier_data:

            alarm_notifier_info_list.append(AlarmInfoCustomerChannel(

                alarm_info=self.get_queryset().filter(id=alarm_info_id).first(),

                notifier=CustomUser.objects.filter(id=notifier.get('id')).first(),

                channel=notifier.get('channel')))

        # 批量插入

        try:

            AlarmInfoCustomerChannel.objects.bulk_create(alarm_notifier_info_list)

            return self.render_to_json_response(status=0, msg='记录成功')

        except Exception as e:

            return self.render_to_json_response(status=1, msg='记录失败%s' % str(e))

表结构

class AlarmInfoCustomerChannel(BaseModel):

    """某告警事件的告警用户和告警渠道"""

    alarm_info = models.ForeignKey(AlarmInfo, verbose_name='告警事件', on_delete=models.CASCADE)

    notifier = models.ForeignKey(CustomUser, verbose_name='告警通知用户', on_delete=models.SET_NULL, null=True)

    channel = models.CharField(verbose_name="通知渠道", max_length=3, null=True, blank=True)

码农公寓

1、数据库表关系

1.一对多

2.多对多

3.一对一

2、sql语句创建关联表

3、ORM生成关联表模型

4、生成数据库表

5、多表操作 添加记录

1.单表

2.一对多，一对一

3、多对多

4、bulk_create多对多，添加

相关文章

　　1.一对多

　　3.一对一

5、多表操作添加记录

　　1.单表

　　2.一对多，一对一