056.Python前端Django模型ORM多表基本操作
阅读原文时间:2023年07月08日阅读:5

1.1 新建一个项目

root@darren-virtual-machine:~# cd /root/PycharmProjects/

root@darren-virtual-machine:~/PycharmProjects# django-admin startproject orm_demo

root@darren-virtual-machine:~/PycharmProjects# ll

drwxr-xr-x 7 root root 4096 4月 5 20:04 mysite/
drwxr-xr-x 3 root root 4096 4月 6 13:27 orm_demo/
drwxr-xr-x 3 root root 4096 4月 5 16:27 pymysql/

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py startapp app01

root@darren-virtual-machine:~/PycharmProjects/orm_demo# ll

drwxr-xr-x 3 root root 4096 4月 6 13:29 app01/
drwxr-xr-x 3 root root 4096 4月 6 13:28 .idea/
-rwxr-xr-x 1 root root 628 4月 6 13:27 manage.py*
drwxr-xr-x 3 root root 4096 4月 6 13:29 orm_demo/

设置setting.py

root@darren-virtual-machine:~/PycharmProjects/orm_demo# cat orm_demo/settings.py

INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'app01.apps.App01Config',
]

1.2 创建一个新的数据库

root@darren-virtual-machine:~/PycharmProjects/orm_demo# mysql -uroot -p123456

mysql> create database orm_demo default charset=utf8;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| django |
| mysql |
| orm_demo |
| performance_schema |
| sys |
+--------------------+

配置setting链接数据库

root@darren-virtual-machine:~/PycharmProjects/orm_demo# cat orm_demo/settings.py

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'orm_demo',
'HOST': '127.0.0.1',
'PORT': 3306,
'USER': "root",
'PASSWORD': "123456",
}
}

导入__init__.py

root@darren-virtual-machine:~/PycharmProjects/orm_demo# cat orm_demo/__init__.py

import pymysql
pymysql.install_as_MySQLdb()

1.3 创建模型

root@darren-virtual-machine:~/PycharmProjects/orm_demo# cat app01/models.py

from django.db import models

Create your models here.

class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=5, decimal_places=2)
pub_date = models.DateField()
publish = models.ForeignKey("Publish", on_delete=models.CASCADE)
authors = models.ManyToManyField("Author")

class Publish(models.Model):
name = models.CharField(max_length=32)
city = models.CharField(max_length=64)
email = models.EmailField()

class Author(models.Model):
name = models.CharField(max_length=32)
age = models.SmallIntegerField()
au_detail = models.OneToOneField("AuthorDetail", on_delete=models.CASCADE)

class AuthorDetail(models.Model):
gender_choices = (
(0, "女"),
(1, "男"),
(2, "保密"),
)
gender = models.SmallIntegerField(choices=gender_choices)
tel = models.CharField(max_length=32)
addr = models.CharField(max_length=64)
birthday = models.DateField()

1.4 执行数据库迁移操作

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py makemigrations

Migrations for 'app01':
app01/migrations/0001_initial.py
- Create model Author
- Create model AuthorDetail
- Create model Publish
- Create model Book
- Add field au_detail to author

root@darren-virtual-machine:~/PycharmProjects/orm_demo# cat app01/migrations/0001_initial.py

# Generated by Django 3.0.5 on 2020-04-06 07:10

from django.db import migrations, models
import django.db.models.deletion

class Migration(migrations.Migration):

initial = True

dependencies = \[  
\]

operations = \[  
    migrations.CreateModel(  
        name='Author',  
        fields=\[  
            ('id', models.AutoField(auto\_created=True, primary\_key=True, serialize=False, verbose\_name='ID')),  
            ('name', models.CharField(max\_length=32)),  
            ('age', models.SmallIntegerField()),  
        \],  
    ),  
    migrations.CreateModel(  
        name='AuthorDetail',  
        fields=\[  
            ('id', models.AutoField(auto\_created=True, primary\_key=True, serialize=False, verbose\_name='ID')),  
            ('gender', models.SmallIntegerField(choices=\[(0, '女'), (1, '男'), (2, '保密')\])),  
            ('tel', models.CharField(max\_length=32)),  
            ('addr', models.CharField(max\_length=64)),  
            ('birthday', models.DateField()),  
        \],  
    ),  
    migrations.CreateModel(  
        name='Publish',  
        fields=\[  
            ('id', models.AutoField(auto\_created=True, primary\_key=True, serialize=False, verbose\_name='ID')),  
            ('name', models.CharField(max\_length=32)),  
            ('city', models.CharField(max\_length=64)),  
            ('email', models.EmailField(max\_length=254)),  
        \],  
    ),  
    migrations.CreateModel(  
        name='Book',  
        fields=\[  
            ('id', models.AutoField(auto\_created=True, primary\_key=True, serialize=False, verbose\_name='ID')),  
            ('title', models.CharField(max\_length=32)),  
            ('price', models.DecimalField(decimal\_places=2, max\_digits=5)),  
            ('pub\_date', models.DateField()),  
            ('authors', models.ManyToManyField(to='app01.Author')),  
            ('publish', models.ForeignKey(on\_delete=django.db.models.deletion.CASCADE, to='app01.Publish')),  
        \],  
    ),  
    migrations.AddField(  
        model\_name='author',  
        name='au\_detail',  
        field=models.OneToOneField(on\_delete=django.db.models.deletion.CASCADE, to='app01.AuthorDetail'),  
    ),  
\]

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py migrate

Operations to perform:
Apply all migrations: admin, app01, auth, contenttypes, sessions
Running migrations:
Applying contenttypes.0001_initial… OK
Applying auth.0001_initial… OK
Applying admin.0001_initial… OK
Applying admin.0002_logentry_remove_auto_add… OK
Applying admin.0003_logentry_add_action_flag_choices… OK
Applying app01.0001_initial… OK
Applying contenttypes.0002_remove_content_type_name… OK
Applying auth.0002_alter_permission_name_max_length… OK
Applying auth.0003_alter_user_email_max_length… OK
Applying auth.0004_alter_user_username_opts… OK
Applying auth.0005_alter_user_last_login_null… OK
Applying auth.0006_require_contenttypes_0002… OK
Applying auth.0007_alter_validators_add_error_messages… OK
Applying auth.0008_alter_user_username_max_length… OK
Applying auth.0009_alter_user_last_name_max_length… OK
Applying auth.0010_alter_group_name_max_length… OK
Applying auth.0011_update_proxy_permissions… OK
Applying sessions.0001_initial… OK

1.5 查看数据库数据

mysql> use orm_demo
mysql> show tables;
+----------------------------+
| Tables_in_orm_demo |
+----------------------------+
| app01_author |
| app01_authordetail |
| app01_book |
| app01_book_authors |
| app01_publish |
| auth_group |
| auth_group_permissions |
| auth_permission |
| auth_user |
| auth_user_groups |
| auth_user_user_permissions |
| django_admin_log |
| django_content_type |
| django_migrations |
| django_session |
+----------------------------+

四个模型,但是创建了五张表,其中自己创建的表的信息如下

mysql> desc app01_book_authors;

+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_id | int(11) | NO | MUL | NULL | |
| author_id | int(11) | NO | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+

查看数据库表的信息

mysql> desc app01_author
-> ;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| age | smallint(6) | NO | | NULL | |
| au_detail_id | int(11) | NO | UNI | NULL | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc app01_authordetail
-> ;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| gender | smallint(6) | NO | | NULL | |
| tel | varchar(32) | NO | | NULL | |
| addr | varchar(64) | NO | | NULL | |
| birthday | date | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> desc app01_book;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(32) | NO | | NULL | |
| price | decimal(5,2) | NO | | NULL | |
| pub_date | date | NO | | NULL | |
| publish_id | int(11) | NO | MUL | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc app01_book_authors;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| book_id | int(11) | NO | MUL | NULL | |
| author_id | int(11) | NO | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc app01_book_publish;
ERROR 1146 (42S02): Table 'orm_demo.app01_book_publish' doesn't exist
mysql> desc app01_book_publish;er
ERROR 1146 (42S02): Table 'orm_demo.app01_book_publish' doesn't exist
-> ^C

^C
mysql> desc app01_book_publisher;
ERROR 1146 (42S02): Table 'orm_demo.app01_book_publisher' doesn't exist
mysql> desc app01_publisher;
ERROR 1146 (42S02): Table 'orm_demo.app01_publisher' doesn't exist
mysql> desc app01_publish;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| city | varchar(64) | NO | | NULL | |
| email | varchar(254) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+

table_information

1.6 向数据库中插入数据

mysql> insert into app01_publish(name,city,email)
-> values
-> ("华山出版社", "华山", "hs@163.com"),
-> ("明教出版社", "黑木崖", "mj@163.com")
-> ;

查看数据

mysql> select * from app01_publish;
+----+-----------------+-----------+------------+
| id | name | city | email |
+----+-----------------+-----------+------------+
| 1 | 华山出版社 | 华山 | hs@163.com |
| 2 | 明教出版社 | 黑木崖 | mj@163.com |
+----+-----------------+-----------+------------+

app01_authordetail表

mysql> insert into app01_authordetail(gender,tel,addr,birthday)
-> values
-> (1,13432335433,"华山","1994-5-23"),
-> (1,13943454554,"黑木崖","1961-8-13"),
-> (0,13878934322,"黑木崖","1996-5-20") ;
Query OK, 3 rows affected (0.00 sec)

查看数据

mysql> select * from app01_authordetail;
+----+--------+-------------+-----------+------------+
| id | gender | tel | addr | birthday |
+----+--------+-------------+-----------+------------+
| 1 | 1 | 13432335433 | 华山 | 1994-05-23 |
| 2 | 1 | 13943454554 | 黑木崖 | 1961-08-13 |
| 3 | 0 | 13878934322 | 黑木崖 | 1996-05-20 |
+----+--------+-------------+-----------+------------+

必许先添加这一步,再插入app01_author,否则报错如下

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`orm_demo`.`app01_author`, CONSTRAINT `app01_author_au_detail_id_440c948c_fk_app01_authordetail_id` FOREIGN KEY (`au_detail_id`) REFERENCES `app01_authordetail` (`id`))

插入app01_author表

mysql> insert into app01_author(name,age,au_detail_id) values ("令狐冲",25,1), ("任我行",58,2), ("任盈盈",23,3) ;

查看

mysql> select * from app01_author;
+----+-----------+-----+--------------+
| id | name | age | au_detail_id |
+----+-----------+-----+--------------+
| 7 | 令狐冲 | 25 | 1 |
| 8 | 任我行 | 58 | 2 |
| 9 | 任盈盈 | 23 | 3 |
+----+-----------+-----+--------------+

各表关系

2.1 插入数据

2.1.1 一对多

出版社和书是一对多关系

设计路由

root@darren-virtual-machine:~/PycharmProjects/orm_demo# cat orm_demo/urls.py

from django.contrib import admin
from django.urls import path
from app01 import views
urlpatterns = [
path('admin/', admin.site.urls),
path('add_book',views.add_book),
]

创建一个html模板

root@darren-virtual-machine:~/PycharmProjects/orm_demo# mkdir templates

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim orm_demo/settings.py

TEMPLATES = [
{
'BACKEND': 'django.template.backends.django.DjangoTemplates',
'DIRS': [os.path.join(BASE_DIR,"templates")],
'APP_DIRS': True,
'OPTIONS': {
'context_processors': [
'django.template.context_processors.debug',
'django.template.context_processors.request',
'django.contrib.auth.context_processors.auth',
'django.contrib.messages.context_processors.messages',
],
},
},
]

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim templates/add_book.html


Title

新增书籍

{% csrf_token %}

书名:

出版社:

价格:

出版时间:

视图文件

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

Create your views here.

def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
#获取数据
title = request.POST.get("title")
publish = request.POST.get("publish")
price = request.POST.get("price")
pub_date = request.POST.get("pub_date")
#方式一,通过传对象的方式,首先获取对象
pub_obj = models.Publish.objects.filter(name=publish).first()
book_obj = models.Book.objects.create(title=title,price=price,pub_date=pub_date,publish=pub_obj)return HttpResponse("新增成功")

访问http://127.0.0.1:8000/add_book/并添加数据

点击提交,查看数据库

使用方式二

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

Create your views here.

def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
#获取数据
title = request.POST.get("title")
publish = request.POST.get("publish")
price = request.POST.get("price")
pub_date = request.POST.get("pub_date")
#方式二,通过id的形式,在publish_id后也可以直接跟数字
pub_obj = models.Publish.objects.filter(name=publish).first()
books = models.Book.objects.create(title=title, price=price, pub_date=pub_date, publish_id=pub_obj.pk)
return HttpResponse("新增成功")

访问http://127.0.0.1:8000/add_book/并添加数据

点击提交

查看数据库

2.1.2 多对多

视图文件

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

Create your views here.

def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
#获取数据
title = request.POST.get("title")
publish = request.POST.get("publish")
price = request.POST.get("price")
pub_date = request.POST.get("pub_date")
#方式一,通过传对象的方式,首先获取对象
#pub_obj = models.Publish.objects.filter(name=publish).first()
#book_obj = models.Book.objects.create(title=title,price=price,pub_date=pub_date,publish=pub_obj)
#books = models.Book.objects.create(title=title, price=price, pub_date=pub_date, publish_id=pub_obj.pk)
#多对多,方式一
book = models.Book.objects.filter(title="独孤九剑").first()
ling = models.Author.objects.filter(name="令狐冲").first()
ying = models.Author.objects.filter(name="任盈盈").first()
book.authors.add(ling, ying)
return HttpResponse("新增成功")

访问http://127.0.0.1:8000/add_book/,直接提交

查看数据库

方式二

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim app01/views.py

from django.shortcuts import render,HttpResponse
from app01 import models

Create your views here.

def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
#获取数据
title = request.POST.get("title")
publish = request.POST.get("publish")
price = request.POST.get("price")
pub_date = request.POST.get("pub_date")
#方式一,通过传对象的方式,首先获取对象
#pub_obj = models.Publish.objects.filter(name=publish).first()
#book_obj = models.Book.objects.create(title=title,price=price,pub_date=pub_date,publish=pub_obj)
#books = models.Book.objects.create(title=title, price=price, pub_date=pub_date, publish_id=pub_obj.pk)
#多对多,方式一
#book = models.Book.objects.filter(title="独孤九剑").first()
#ling = models.Author.objects.filter(name="令狐冲").first()
#ying = models.Author.objects.filter(name="任盈盈").first()
#book.authors.add(ling, ying)
#方式二
book = models.Book.objects.filter(title="独孤九剑").first()
ling = models.Author.objects.filter(name='令狐冲').first()
ying = models.Author.objects.filter(name='任我行').first()
book.authors.add(ling.pk, ying.pk)
return HttpResponse("新增成功")

访问http://127.0.0.1:8000/add_book/,直接提交

查看数据库

2.2 删除数据

使用clear,先查出一个对象,然后移除相应关系

views  视图文件

通过作者删除与书籍的关系

from django.shortcuts import render,HttpResponse
from app01 import models

Create your views here.

def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
author_obj = models.Author.objects.filter(au_detail_id=1).first()
print(author_obj)
author_obj.book_set.clear()
return HttpResponse("success")

访问http://127.0.0.1:8000/add_book/,直接提交

查看数据库

通过出版社删除书籍,使用remove方法

from django.shortcuts import render,HttpResponse
from app01 import models

Create your views here.

def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
pub_obj = models.Publish.objects.get(pk=1)
book_obj = models.Book.objects.get(pk=1)
pub_obj.book_set.remove(book_obj)
return HttpResponse("新增成功")

然后

访问http://127.0.0.1:8000/add_book/,直接提交

没有remove方法,必须在model设定,可以允许null值,才会有remove方法

root@darren-virtual-machine:~/PycharmProjects/orm_demo# vim app01/models.py

class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=5, decimal_places=2)
pub_date = models.DateField()
publish = models.ForeignKey("Publish", on_delete=models.CASCADE,null=True)
authors = models.ManyToManyField("Author")

再次访问

需要重新执行迁移数据库操作

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py makemigrations

root@darren-virtual-machine:~/PycharmProjects/orm_demo# python3 manage.py migrate

访问http://127.0.0.1:8000/add_book/,直接提交

2.3 修改数据

set()方法

先清空,在设置,编辑书籍时即可用到。

book = models.Book.objects.filter(title="独孤九剑").first()
book.authors.set([1, 3]) 

基本操作结束