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    |                |
+-----------+---------+------+-----+---------+----------------+

查看数据库表的信息

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

<!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])

基本操作结束

posted @ 2020-04-06 20:09 梦中泪阅读(275) 评论(0) 编辑收藏举报

刷新页面返回顶部

战五渣

056.Python前端Django模型ORM多表基本操作

一准备工作

1.1 新建一个项目

1.2 创建一个新的数据库

1.3 创建模型

1.4 执行数据库迁移操作

1.5 查看数据库数据

1.6 向数据库中插入数据

各表关系

二对数据库数据进行操作

2.1 插入数据

2.1.1 一对多

使用方式二

2.1.2 多对多

2.2 删除数据

2.3 修改数据

公告

战五渣

056.Python前端Django模型ORM多表基本操作

一 准备工作

1.1 新建一个项目

1.2 创建一个新的数据库

1.3 创建模型

1.4 执行数据库迁移操作

1.5 查看数据库数据

1.6 向数据库中插入数据

各表关系

二 对数据库数据进行操作

2.1 插入数据

2.1.1 一对多

使用方式二

2.1.2 多对多

2.2 删除数据

2.3 修改数据

公告

一准备工作

二对数据库数据进行操作