django的基本使用-ORM(二)-七、多表操作

时间：2024-02-17 11:16:41

1.一对多操作

1.1 创建my_publish表。

mysql> use mydatabase
mysql> create table my_publish(id int(10) primary key auto_increment,name varchar(20),addr varchar(50),email varchar(20));
mysql> desc my_publish;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| addr  | varchar(50) | YES  |     | NULL    |                |
| email | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

1.2.在my_publish表中插入数据

insert into my_publish values(0,'苹果出版社','北京',123),(0,'西瓜出版社','南京',456);
mysql> select * from my_publish;
+------+-----------------+--------+-------+
| id   | name            | addr   | email |
+------+-----------------+--------+-------+
|    1 | 苹果出版社        | 北京   | 123    |
|    2 | 西瓜出版社        | 南京   | 456    |
+------+-----------------+--------+-------+
2 rows in set (0.00 sec)

1.3.创建my_book表

create table my_book(id int(10) primary key auto_increment,title varchar(20),price double(5,2),publishDate datetime,publish_id int(10),foreign key(publish_id)
references my_publish(id));

查看创建的表结构

mysql> desc my_book;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(10)     | NO   | PRI | NULL    | auto_increment |
| title       | varchar(20) | YES  |     | NULL    |                |
| price       | double(5,2) | YES  |     | NULL    |                |
| publishDate | datetime    | YES  |     | NULL    |                |
| publish_id  | int(10)     | YES  | MUL | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

1.4 手动插入数据进行测试

mysql> insert into my_book values(0,"西游记",199.00,now(),1);
Query OK, 1 row affected (0.00 sec)

# 查看数据是否插入成功
mysql> select * from my_book;
+------+-----------+--------+---------------------+------------+
| id   | title     | price  | publishDate         | publish_id |
+------+-----------+--------+---------------------+------------+
|    1 | 西游记    | 199.00 | 2024-02-11 21:08:50 |          1 |
+------+-----------+--------+---------------------+------------+
1 row in set (0.00 sec)

1.5 建立模型类

编辑app1应用下的models.py文件

from django.db import models

# 创建my_publish表的模型类
class my_publish(models.Model):
    class Meta:
        db_table = 'my_publish' #这里的表名称一定要和数据库中表名保持一致

    id = models.AutoField(max_length=10,primary_key=True)
    name = models.CharField(max_length=20)
    addr = models.CharField(max_length=50)
    email = models.CharField(max_length=50)


# 创建my_book表的模型类
class my_book(models.Model):
    class Meta:
        db_table = 'my_book'  # 这里的表名称一定要和数据库中表名保持一致

    id = models.AutoField(max_length=10,primary_key=True)
    title = models.CharField(max_length=20)
    price = models.FloatField()
    # auto_now_add 新增数据库数据库的时候才会触发这个时间，修改数据不会触发，修改要触发时间需要使用auto_now=True
    publishDate = models.DateTimeField(auto_now_add=True)
    
    #注意： publish 是外键，外键的名称django会自动补充上"_id".正好和数据库中的字段pubish_id字段对应上
    publish = models.ForeignKey(my_publish,null=True,on_delete=models.SET_NULL)

1.6 使用视图查询my_book数据

这里没有直接对my_book表直接进行插入数据操作而是先查询，原因是先看上边的的模型类定义的是否正确

from .models import *
from django.db.models import Q
from django.http import  HttpResponse

def fun_app1(request):
    list = my_book.objects.all()
    for i in  list:
        print(i.id,i.title,i.price,i.publishDate,i.publish_id)

    return  HttpResponse("查询数据成功")

1.7 使用视图插入数据

在app1应用下的视图函数

from .models import *
from django.http import  HttpResponse

def fun_app1(request):
    # 这里要注意，获取id时需要使用get，不能使用fiter,因为get返回的是结果，filter返回是查询对象集合
    # 这里pk是自动指向my_publish的主键。pk是primary key的简写。在django中pk是固定写法
    publish_id = my_publish.objects.get(pk=2)

    my_book.objects.create(title="水浒传",price="88.00",publish=publish_id)
    return  HttpResponse("插入数据成功")

查看数据库数据如下：

mysql> select * from my_book;
+----+-----------+--------+---------------------+------------+
| id | title     | price  | publishDate         | publish_id |
+----+-----------+--------+---------------------+------------+
|  1 | 西游记    | 199.00 | 2024-02-11 23:08:45 |          1  |
|  2 | 水浒传    |  88.00 | 2024-02-12 04:25:09 |          2  |
+----+-----------+--------+---------------------+------------+
2 rows in set (0.00 sec)-+

另外一种写法前边也介绍过了

from .models import *
from django.http import  HttpResponse

def fun_app1(request):
    publish_id = my_publish.objects.get(pk=2)

    book1 = my_book(title="三国演义",price="188.00",publish=publish_id)
    book1.save()
    return  HttpResponse("插入数据成功")

结果如下：

mysql> select * from my_book;
+----+--------------+--------+---------------------+------------+
| id | title        | price  | publishDate         | publish_id |
+----+--------------+--------+---------------------+------------+
|  1 | 西游记       | 199.00 | 2024-02-11 23:08:45 |          1  |
|  2 | 水浒传       |  88.00 | 2024-02-12 04:25:09 |          2  |
|  3 | 三国演义     | 188.00 | 2024-02-12 04:30:24 |          2  |
+----+--------------+--------+---------------------+------------+

相关文章