056.Python前端Django模型ORM多表基本操作
一 准备工作
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 | | +-----------+---------+------+-----+---------+----------------+
查看数据库表的信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 | | +-------+--------------+------+-----+---------+----------------+
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
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <h3>新增书籍</h3> <form action="" method="post"> {% csrf_token %} <p>书名:<input type="text" name="title"></p> <p>出版社:<input type="text" name="publisher"></p> <p>价格:<input type="text" name="price"></p> <p>出版时间:<input type="text" name="pub_date"></p> <input type="submit"> </form> </body> </html>
视图文件
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])
基本操作结束
---------------------------------------------------------------------------
个性签名:我以为我很颓废,今天我才知道,原来我早报废了。
如果觉得本篇文章最您有帮助,欢迎转载,且在文章页面明显位置给出原文链接!记得在右下角点个“推荐”,博主在此感谢!