Django数据库
一、MySQL驱动程序安装
我们使用Django来操作MySQL，实际上底层还是通过python来操作的。因此我们想要用Django来操作MySQL，首先还是需要安装一个驱动程序。在Python3中，驱动程序有多种选择。比如pymysql以及mysqlclient等。这里我们就使用mysqlclient来操作。mysqlclient安装非常简单，只需要通过pip install mysqlclient即可安装。
虚拟环境安装
常见MySQL驱动介绍：
1、MySQL-python：也就是MySQLdb。是对c语言操作MySQL数据库的一个简单封装。遵循了Python DB API v2。但是只支持Python2，目前还不支持Python3。
2、mysqlclient：是MySQL-python的另一个分支。支持Python3并修复了一些bug。
3、pymysql：纯Python实现的一个驱动。因为是纯Python编写的，因此执行效率不如MySQL-python，并且也因为是纯python编写的，因此可以和Python实现无缝衔接。
4、MySQL Connector/Python：MySQL官方推出的使用纯Python连接MySQL的驱动。因为是纯Python开发的，效率不高。
二、Django操作数据库
在Django中操作数据库有两种方式，第一种方式是使用原生sql语句来操作数据库，第二种是使用ORM模型来操作。
原生SQL语句操作数据库
在Django中使用原生sql语句操作其实就是使用Python db api的接口来操作，如果你的mysql驱动是pymysql，那么你就是使用pymysql来操作的，只不过Django将数据库连接的这一部分封装好了，我们只要在settings.py中配置好了数据库连接信息后直接使用Django封装好的接口就可以操作了。
首先创建一个数据库
create database django_db1;
新建表
use django_db1;
create table book(
id int primary key auto_increment,
name varchar(20),
author varchar(20)
)char set=utf8;
新建项目db_operation_demo
在外部库中可以看到Django可以使用四种数据库MySQL、Oracle、postgresql、sqlite3
在settings.py设置数据库引擎
DATABASES = {
"default": {
# 数据库引擎:sqlite3/mysql/oracle
"ENGINE": "django.db.backends.mysql",
# 数据库名
"NAME": 'django_db1',
# 数据库用户名
'USER':'root',
# 数据库密码
'PASSWORD':'123456',
# 数据库的主机地址
'HOST':'127.0.0.1',
# 数据库端口号
'PORT':'3306',
}
}
通过游标对象实现插入数据、查找数据
新建文件views.py
from django.db import connection
from django.shortcuts import render
def index(request):
cursor = connection.cursor()
cursor.execute("insert into book(id,name,author) values(null,'三国演义','罗贯中')")
cursor.execute("insert into book(id,name,author) values(null,'水浒传','施耐庵')")
cursor.execute("select id,name,author from book")
#返回一条数据
row=cursor.fetchone()
for r in row:
print(r)
#返回所有数据
rows=cursor.fetchall()
return render(request, 'index.html')
urls.py
from django.urls import path
from djangoProject import views
urlpatterns = [
path("", views.index,name="index"),
]
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
</body>
</html>
执行程序
访问http://localhost:8000/
三、图书管理系统
创建数据库及表
create database book_manager charset utf8;
use book_manager;
create table book(
id int primary key auto_increment,
name varchar(20) not null,
author varchar(20) not null
)char set=utf8;
创建book_manager项目，添加front.app
项目结构：
在settings.py中新加入front模块
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"front"
]
在settings.py中关闭CSRF检查
MIDDLEWARE = [
"django.middleware.security.SecurityMiddleware",
"django.contrib.sessions.middleware.SessionMiddleware",
"django.middleware.common.CommonMiddleware",
# "django.middleware.csrf.CsrfViewMiddleware",
"django.contrib.auth.middleware.AuthenticationMiddleware",
"django.contrib.messages.middleware.MessageMiddleware",
"django.middleware.clickjacking.XFrameOptionsMiddleware",
]
在settings.py中引入Mysql数据库
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'book_manager',
# 数据库用户名
'USER':'root',
# 数据库密码
'PASSWORD':'123456',
# 数据库的主机地址
'HOST':'127.0.0.1',
# 数据库端口号
'PORT':'3306'
}
}
配置网页的urls.py
from django.urls import path
from front import views
urlpatterns = [
path("", views.index, name="index"),
path("add_book", views.add_book, name="add_book"),
path("book_detail/<int:book_id>/", views.book_detail, name="book_detail"),
path("delete_book/", views.delete_book, name="delete_book"),
path("edit_book_before/", views.edit_book_before, name="edit_book_before"),
path("edit_book_after/", views.edit_book_after, name="edit_book_after"),
]
配置控制层views.py，实现增删改查业务
from django.shortcuts import render, redirect, reverse
from django.db import connection
# Create your views here.
def get_cursor():
return connection.cursor()
def index(request):
cursor = get_cursor()
cursor.execute("select id,name,author from book")
books = cursor.fetchall()
#[(1,'三国演义','罗贯中')]
return render(request,"index.html",context={'books':books})
def add_book(request):
if request.method == "GET":
return render(request,"add_book.html")
else:
name = request.POST.get("name")
author = request.POST.get("author")
cursor = get_cursor()
cursor.execute("insert into book (name,author) values (%s,%s)",(name,author))
return redirect(reverse('index'))
def book_detail(request,book_id):
cursor = get_cursor()
cursor.execute("select id,name,author from book where id = %s",(book_id,))
book=cursor.fetchone()
return render(request,"book_detail.html",context={"book":book})
def delete_book(request):
if request.method == "POST":
book_id = request.POST.get("book_id")
cursor = get_cursor()
cursor.execute("delete from book where id = %s",(book_id,))
return redirect(reverse('index'))
else:
raise RuntimeError("删除图书method异常")
def edit_book_before(request):
book_id = request.POST.get("book_id")
cursor = get_cursor()
cursor.execute("select id,name,author from book where id = %s",(book_id,))
book = cursor.fetchone()
return render(request,"book_edit.html",context={"book":book})
def edit_book_after(request):
book_id = request.POST.get("book_id")
name = request.POST.get("name")
author = request.POST.get("author")
cursor = get_cursor()
cursor.execute("update book set name = %s,author = %s where id = %s",(name,author,book_id))
return redirect(reverse('index'))
配置CSS样式
*{
margin: 0;
padding: 0;
}
.nav{
background: #3a3a3a;
height: 65px;
overflow: hidden;
}
.nav li{
float: left;
list-style: none;
margin: 0 0 20px 20px;
line-height: 65px;
}
.nav li a{
color: #fff;
text-decoration: none;
}
.nav li a:hover{
color: lightblue;
}
配置基础页面base.html，是所有页面共有的网页代码
{% load static %}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>图书管理系统</title>
<link rel="stylesheet" href="{% static 'front/base.css' %}">
</head>
<body>
<nav>
<ul class="nav">
<li><a href="/">首页</a></li>
<li><a href="{% url 'add_book' %}">发布图书</a></li>
</ul>
</nav>
{% block content %}
{% endblock %}
</body>
</html>
配置index.html主页面
{% extends 'base.html' %}
{% block content %}
<table>
<thead>
<tr>
<th>序号</th>
<th>书名</th>
<th>作者</th>
<th>编辑</th>
</tr>
</thead>
<tbody>
{% for book in books %}
<tr>
<td>{{ forloop.counter }}</td>
<td><a href="{% url 'book_detail' book_id=book.0 %}">{{ book.1 }}</a></td>
<td>{{ book.2 }}</td>
<td>
<form action="{% url 'edit_book_before' %}" method="post">
<input type="hidden" name="book_id" value="{{ book.0 }}">
<input type="hidden" name="name" value="{{ book.1 }}">
<input type="hidden" name="author" value="{{ book.2 }}">
<input type="submit" value="编辑图书" />
</form>
</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endblock %}
配置图书详情页面book_detail.py
{% extends 'base.html' %}
{% block content %}
<p>书名：{{ book.1 }}</p>
<p>书名：{{ book.2 }}</p>
<form action="{% url 'delete_book' %}" method="post">
<input type="text" name="book_id" value="{{ book.0 }}">
<input type="submit" value="删除图书" />
</form>
{% endblock %}
配置编辑页面book_edit.html
{% extends 'base.html' %}
{% block content %}
<p>书名：{{ book.1 }}</p>
<p>书名：{{ book.2 }}</p>
<form action="{% url 'edit_book_after' %}" method="post">
<input type="text" name="book_id" value="{{ book.0 }}">
<input type="text" name="name" value="{{ book.1 }}">
<input type="text" name="author" value="{{ book.2 }}">
<input type="submit" value="编辑图书" />
</form>
{% endblock %}
配置增加图书页面add_book.html
{% extends 'base.html' %}
{% block content %}
<form action="" method="post">
<table>
<tbody>
<tr>
<td>书名:</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>作者:</td>
<td><input type="text" name="author"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="提交"></td>
</tr>
</tbody>
</table>
</form>
{% endblock %}
运行结果如下：
增加图书
修改图书
删除图书
四、ORM模型
随着项目越来越大，采用原生SQL的方式在代码中会出现大量的SQL语句，那么问题就出现了：
1、SQL语句重复利用率不高，越复杂的SQL条件越多，代码就会越长。会出现很多相似的SQL语句
2、很多SQL语句是在业务逻辑中拼出来的，如果有数据库需要更改，就需要去修改这些逻辑，这会很容易漏掉对某些SQL语句的修改
3、写SQL是容易忽略web安全问题，给未来造成隐患
ORM，全称Object Relational Mapping，中文叫做对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不用再写原生的SQL语句。通过把表映射成类，把行作实例，把字段作为属性，ORM在执行对象操作的时候最终还是会把对应的操作转换为数据库的原生语句。使用ORM有很多优点：
1、易用性：使用ORM做数据库的开发可以有效的减少重复的SQL语句的概率，写出来的模型也更加的直观、清晰
2、性能损耗小：ORM转换成底层数据库操作指令确实会有一些开销。但从实际的情况来看，这种损耗很小（不足5%），只要不是对性能有严苛的要求，综合考虑开发效率、代码的阅读性，带来的好处要远远大于性能损耗，而且项目越大作用越明显。
3、设计灵活：可以轻松的写出复杂的查询。
4、可移植性：Django封装了底层的数据库实现，支持多个关系数据库引擎，包括流行的SQL、PostgreSQL和SQLite，可以非常轻松的切换数据库。
创建ORM模型
ORM模型一般都是放在app的models.py文件中。每个app都可以拥有自己的模型，并且如果这个模型想要映射到数据库中，那么这个app必须要放在settings.py的INSTALLED_APP中进行安装。示例代码如下：
首先创建一个数据库，
create database orm_intro charset utf8;
use orm_intro;
创建orm_intro_demo项目，并在项目中添加bookapp
项目结构如下：
首先在models.py中创建映射数据库中表book的类，models.py。如果要将一个普通的类编成一个可以映射到数据库中的ORM模型，那么必须要将父类设置为models.Model或者他的子类
在settings.py中配置数据库
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_intro',
# 数据库用户名
'USER':'root',
# 数据库密码
'PASSWORD':'123456',
# 数据库的主机地址
'HOST':'127.0.0.1',
# 数据库端口号
'PORT':'3306'
}
}
并配置Installed_apps
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"book"
]
注释掉settings.py中CSRF的检查
在models中创建book类
models.py
from django.db import models
# Create your models here.
# 如果要将一个普通的类编成一个可以映射到数据库中的ORM模型，那么
# 必须要将父类设置为models.Model或者他的子类
class Book(models.Model):
# 1、id:int类型，是自增长的
id = models.AutoField(primary_key=True)
# 2、name:varchar(100),图书的名字
name=models.CharField(max_length=100,null=False)
# 3、author:varchar(100)，图书的作者
author = models.CharField(max_length=100, null=False)
# 4、price:float,图书的价格
price = models.FloatField(null=False,default=0)
将book模型映射到数据库中，需要执行两个命令
1、使用makemigrations生成迁移脚本文件 python manage.py makemigrations
2、使用migrate将新生成的迁移脚本文件映射到数据库中python manage.py migrate
由此在数据库中可以看到很多的表跟项目settings中的APPS对应
此时也生成了ORM对应的book_book表，对应ORM模型
在models.py中新增加一个Publisher模型
models.py
from django.db import models
# Create your models here.
# 如果要将一个普通的类编成一个可以映射到数据库中的ORM模型，那么
# 必须要将父类设置为models.Model或者他的子类
class Book(models.Model):
# 1、id:int类型，是自增长的
id = models.AutoField(primary_key=True)
# 2、name:varchar(100),图书的名字
name=models.CharField(max_length=100,null=False)
# 3、author:varchar(100)，图书的作者
author = models.CharField(max_length=100, null=False)
# 4、price:float,图书的价格
price = models.FloatField(null=False,default=0)
class Publisher(models.Model):
name = models.CharField(max_length=100,null=False)
address = models.CharField(max_length=100,null=False)
重新执行python manage.py makemigrations
在项目中就会新生成一个映射
然后执行python manage.py migrate在数据库中会新生成publisher表
ORM模型的增删改查
创建数据库base_orm_operate
create database base_orm_operate charset utf8;
新建一个项目base_orm_operate，并添加book app
将book app添加到settings.py的INSTALLED_APPS中
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"book"
]
设置settings.py中的数据库配置
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'base_orm_operate',
# 数据库用户名
'USER':'root',
# 数据库密码
'PASSWORD':'123456',
# 数据库的主机地址
'HOST':'127.0.0.1',
# 数据库端口号
'PORT':'3306'
}
}
在models.py中配置Book类
from django.db import models
# Create your models here.
class Book(models.Model):
name=models.CharField(max_length=100,null=False)
author=models.CharField(max_length=100,null=False)
price=models.FloatField(default=0)
然后映射到数据库中
可以看到数据库中生成了映射的表
配置urls.py
from django.urls import path
from book import views
urlpatterns = [
path("", views.index, name="index"),
]
增加数据
只要使用ORM模型创建一个对象，然后在调用这个ORM模型的save方法就可以保存新数据了。
在views.py中定义视图函数
from django.http import HttpResponse
from .models import Book
# Create your views here.
def index(request):
# 1、使用ORM添加两条数据进入数据库中
books1 = Book(name='三国演义',author='罗贯中',price=100)
books1.save()
books2 = Book(name='西游记', author='吴承恩', price=100)
books2.save()
return HttpResponse("书籍添加成功")
运行结果如下：
访问http://localhost:8000/
查看数据库数据
查询数据
所有的查找工作都是使用模型上的"Objects"属性来完成的，当然也可以自定义查询对象。
1、根据主键进行查找，使用主键进行查找，可以使用"Objects.get"方法，然后传递"pk=xx"的方式进行查找。
2、根据其他字段进行查找，可以使用"Objects.filter"方法进行查找，然后传递其他属性的方式进行查找。使用filter方法返回的是一个QuerySet对象，可以使用这个对象的first方法获取第一个值。
在views.py中定义视图函数
from django.http import HttpResponse
from .models import Book
# Create your views here.
def index(request):
# 2、 查询
# 2.1 根据主键进行查找
book3 = Book.objects.get(id=4)
print(book3)
# 2.2 根据name值进行查找
book4 = Book.objects.filter(name='三国演义')
print(book4)
return HttpResponse("书籍添加成功")
运行结果如下：
删除数据
首先查找到对应的数据模型，然后在执行这个模型的"delete"方法即可删除。
在views.py中定义视图函数
from django.http import HttpResponse
from .models import Book
# Create your views here.
def index(request):
# 3、删除数据
book5 = Book.objects.get(pk=5)
book5.delete()
return HttpResponse("书籍添加成功")
运行结果如下：
修改数据
首先查找到对应的数据模型，然后修改这个模型上的属性的值，然后再执行save方法即可修改完成。
在views.py中定义视图函数
from django.http import HttpResponse
from .models import Book
# Create your views here.
def index(request):
# 4、修改数据
book6 = Book.objects.get(id=4)
book6.price = 200
book6.save()
return HttpResponse("书籍添加成功")
运行结果如下：
数据库中的数据更新为200
模型常用属性
常用字段
在Django中，定义了一些Field来与数据库表中的字段类型来进行映射，以下介绍常用的字段类型
AutoField：
映射到数据库中是int类型，可以有自动增长的特性，一般不需要使用这个类型，如果不指定主键，那么模型会自动的生成一个叫做id的自动增长的主键。如果你想指定一个其他名字的并且具有自动增长的主键，使用AutoField也是可以的。
BigAutoField：
64位的整型，类似于AutoFields，只不过是产生的数据范围是从1-9223372036854775807。
BooleanField：
在模型层面接受的是True/False。在数据库层面是tinyint类型，如果没有指定默认值，默认值是none。
CharField：
在数据库层面是varchar类型，在python层面就是普通的字符串。这个类型在使用的时候必须要指定最大的长度，也即必须要传递max_length这个关键字参数进去。
创建数据库表
create database orm_field_demo charset utf8;
新建项目orm_field_demo，添加article app
在settings.py中配置新加入的app
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"article"
]
在settings.py中配置数据库
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_field_demo',
# 数据库用户名
'USER': 'root',
# 数据库密码
'PASSWORD': '123456',
# 数据库的主机地址
'HOST': '127.0.0.1',
# 数据库端口号
'PORT': '3306'
}
}
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField()
title = models.CharField(max_length=255)
反向生成数据库表
可以看到生成表的数据类型和数据结构
配置urls.py
from django.urls import path
from article import views
urlpatterns = [
path("", views.index, name="index"),
]
配置views.py
from django.http import HttpResponse
from .models import Article
# Create your views here.
def index(request):
articles = Article(removed=False)
articles.title="考试"
articles.save()
return HttpResponse("Success")
执行结果如下：
如果没有指定null=True，则默认不能为空，否则会报错
改写models.py如下：
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.NullBooleanField()
title = models.CharField(max_length=255)
这样可以插入为Null的数据
改写完后重新映射数据库，但是python4.0之后的版本不支持NullBooleanField关键字了，所以重新更改django版本。
django-admin --version 查看Django版本的命令行
使用python manage.py makemigrations和python manage.py migrate重新映射表
改写views.py
from django.http import HttpResponse
from .models import Article
# Create your views here.
def index(request):
articles = Article(removed=False)
articles.title="考试"
articles.save()
articles1 = Article(removed=None)
articles1.title = "傻狗"
articles1.save()
return HttpResponse("Success")
然后执行程序，访问http://localhost:8000/
可以看到数据库中可以插入为Null的数据
如果文章标题长度大于254，可以使用TextField替代CharField
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
navie时间和aware时间
1、navie时间：不知道自己的时间是哪个时区的
2、aware时间：知道自己的时间是哪个时区的
首先现在Linux上安装Python，使用命令行sudo apt-get update/sudo apt-get install python3
使用命令行sudo apt-get install pip
pytz专门用于处理时区的库，这个库在安装django的时候默认安装
使用pip list可以看到Linux上安装了pytz的库
可以在Linux中获取本地的时间，这个时间就是naive time，没有时区的时间。
import pytz
from datetime import datetime
now=datetime.now()
now
astimezone方法：此方法用于转换时区，转换时区后时间为aware time。这个方法只能被aware类型的时间调用。
import pytz
from datetime import datetime
utc_timezone=pytz.timezone("UTC")
now=datetime.now()
now.astimezone(utc_timezone)
replace方法：可以对时间中的某些属性进行更改，可以更改时区为亚洲上海的时间
import pytz
from datetime import datetime
utc_timezone=pytz.timezone("UTC")
now=datetime.now()
now.astimezone(utc_timezone)
now.replace(tzinfo=pytz.timezone("Asia/shanghai"))
在实际操作Django时间时，在settings.py文件中，将USE_TZ设置为True
LANGUAGE_CODE = "en-us"
TIME_ZONE = "UTC"
USE_I18N = True
USE_TZ = True
可以查看datetime.now()的源码，看到now函数返回的时间是navie time。
@classmethod
def now(cls, tz=None):
"Construct a datetime from time.time() and optional time zone info."
t = _time.time()
return cls.fromtimestamp(t, tz)
DateField：
日期类型。在Python中是datetime.date类型，可以记录年月日。在映射到数据库中也是date类型。使用这个Field可以传递以下几个参数：
1、auto_now：在每次这个数据保存的时候，都使用当前的时间。比如作为一个记录修改日期的字段，可以将这个属性设置为True。
2、auto_now_add：在每次数据第一次被添加进去的时候，都使用当前的时间。比如作为一个记录第一次入库的字段，可以将这个属性设置为True。
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
create_time=models.DateTimeField(auto_now_add=True)
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
在数据库中就生成了create_time字段
在views.py中设置create_time字段
from django.http import HttpResponse
from django.utils.timezone import now
from .models import Article
# Create your views here.
def index(request):
articles = Article(title='测试DateTimeField',create_time=now())
articles.save()
return HttpResponse("Success")
运行程序：可以看到插入了一行数据
默认获取的时间是UTC的时间
修改views.py，引入localtime， 本地化时间
from django.http import HttpResponse
from django.utils.timezone import now,localtime
from .models import Article
# Create your views here.
def index(request):
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
return HttpResponse("Success")
修改settings.py，修改时区为上海时区
LANGUAGE_CODE = "en-us"
TIME_ZONE = "Asia/shanghai"
USE_I18N = True
USE_TZ = True
django.util.timezone.now方法
会根据settings.py中是否设置了"USE_TZ=True"会获取当前的时间，如果设置了，那么就会获取一个aware类型的UTC时间，如果没有设置，会获取一个navie类型的时间
django.util.timezone.localtime方法
会根据settings.py中的TIME_ZONE来将一个aware类型的时间转换为TimeZone指定时区的时间。
重新执行程序：
在模版中渲染本地时间
views.py
from django.http import HttpResponse
from django.shortcuts import render
from django.utils.timezone import now,localtime
from .models import Article
# Create your views here.
def index(request):
# articles = Article(title='测试DateTimeField',create_time=now())
# articles.save()
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
# return HttpResponse("Success")
return render(request,'index.html',context={'create_time':create_time})
新建index.html
{% load tz %}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
{{ create_time | localtime }}
</body>
</html>
运行程序：可以看到DTL会将UTC时间转换为TIME_ZONE 时间。
EmailField：
类似于CharField，在数据库底层是一个varchar类型，最大长度是254个字符。
在models.py中新加一个Person类
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
email=models.EmailField()
映射数据库的表
urls.py
from django.urls import path
from article import views
urlpatterns = [
path("", views.index, name="index"),
path("/email",views.email,name="email"),
]
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
views.py
from django.http import HttpResponse
from django.shortcuts import render
from django.utils.timezone import now,localtime
from .models import Article,Person
# Create your views here.
def index(request):
# articles = Article(title='测试DateTimeField',create_time=now())
# articles.save()
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
# return HttpResponse("Success")
return render(request,'index.html',context={'create_time':create_time})
def email(request):
Person1 = Person()
Person1.email="359269746@qq.com"
Person1.save()
return HttpResponse("Success")
访问http://localhost:8000/email
但是EmailField不会验证插入的email数据是否是email，而是当做字符串进行插入
FileField：
用来存储文件。
ImageField：
用来存储图片文件。
FloatField：
浮点类型，映射到数据库中是float类型。
IntegerField：
整型。值的区间是-2147483648-2147483647
BigIntergerField：
大整形。
PositiveIntegerField：
正整型
SmallIntegerField：
小整型
PositiveSmallIntegerField：
正小整型。
TextField：
大量的文本类型。
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
查看数据库
UUIDField：
只能存储uuid格式的字段。uuid是一个32位的全球唯一的字符串，一般用来作为主键。
URLField：
类似于CharField。只不过只能用来存储url格式的字符串，并且默认的max_length是200。
Field的常用参数
null：
如果设置为True,Django将会在映射表的时候指定是否为空，默认是false，在使用字符串相关的Field（CharField/TextField）的时候，官方推荐尽量不要使用这个参数，也就是保持默认值False，因为Django在处理字符串相关的Field的时候，即使这个Field的null=False，如果你没有给这个Field传递任何值，那么Django也会使用一个空的字符串""来作为默认值存储进去。因此如果再使用null=True，Django会产生两种空值的情形（NULL或者空字符串）。如果想要在表单验证的时候允许这个字符串为空，那么建议使用blank=True。如果你的Field是BooleanField，那么对应的可空字段为NullBooleanField。
新加入一个models
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
username=models.CharField(max_length=100)
age=models.IntegerField(null=True)
默认username不为null，age可以为null
views.py
from django.http import HttpResponse
from django.shortcuts import render
from django.utils.timezone import now,localtime
from .models import Article, Person, Author
# Create your views here.
def index(request):
# articles = Article(title='测试DateTimeField',create_time=now())
# articles.save()
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
# return HttpResponse("Success")
return render(request,'index.html',context={'create_time':create_time})
def email(request):
Person1 = Person()
Person1.email="359269746@qq.com"
Person1.save()
return HttpResponse("Success")
def null_text_field_view(request):
author=Author(username='')
author.save()
return HttpResponse("Success")
urls.py
from django.urls import path
from article import views
urlpatterns = [
path("", views.index, name="index"),
path("email",views.email,name="email"),
path("null",views.null_text_field_view,name="null"),
]
访问http://localhost:8000/null
数据库中数据，这里面的username是一个空值，age是null
Blank:
标识这个字段在表单验证的时候是否可以为空，默认为False。这个和null有区别的，null是一个春数据库级别的，而Blank是表单验证级别的。null会影响到数据库的结构，blank不会影响到数据库结构。
db_column:
这个字段是在数据库中的名字，如果没有设置这个参数，那么将会使用模型中属性的名字。
未设置db_column属性前
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
username=models.CharField(max_length=100)
age=models.IntegerField(null=True)
数据库中表的对应字段
设置db_column属性后
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
username=models.CharField(max_length=100)
age=models.IntegerField(null=False,db_column='author_age')
原来数据库的列会更改列名
default：
默认值，可以为一个值，或者是一个函数，但是不支持lambda表达式，并且不支持列表/字典/集合等可变的数据结构。
drop table article_author;
create table article_author(
id integer auto_increment primary key,
username varchar(100),
author_age integer,
create_time datetime
)char set=utf8mb4;
更改models.py，将年龄改为默认缺省值为0
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
username=models.CharField(max_length=100)
age=models.IntegerField(null=False,db_column='author_age',default=0)
更改views.py，插入username为zhiliao的数据
from django.http import HttpResponse
from django.shortcuts import render
from django.utils.timezone import now,localtime
from .models import Article, Person, Author
# Create your views here.
def index(request):
# articles = Article(title='测试DateTimeField',create_time=now())
# articles.save()
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
# return HttpResponse("Success")
return render(request,'index.html',context={'create_time':create_time})
def email(request):
Person1 = Person()
Person1.email="359269746@qq.com"
Person1.save()
return HttpResponse("Success")
def null_text_field_view(request):
author=Author(username='zhiliao')
author.save()
return HttpResponse("Success")
运行结果如下：
可以看到数据库中插入了一条username为zhiliao，缺省值为0的值
数据库表中加入create_time字段，传入一个函数
修改models.py
from django.db import models
from django.utils.timezone import now
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
id = models.BigAutoField(primary_key=True)
username=models.CharField(max_length=100)
age=models.IntegerField(null=True,db_column='author_age',default=0)
create_time=models.DateTimeField(default=now)
运行程序，访问http://localhost:8000/null
插入了一条数据，默认create_time时间为now
primary_key：
是否为主键，默认为False。
unique:
在表中这个字段的值是否唯一，一般是设置手机号码/邮箱等。
更改model.py，添加手机号
from django.db import models
from django.utils.timezone import now
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
id = models.BigAutoField(primary_key=True)
username=models.CharField(max_length=100)
age=models.IntegerField(null=True,db_column='author_age',default=0)
create_time=models.DateTimeField(default=now)
telephone=models.CharField(max_length=11,unique=True,db_column='author_telephone',null=True)
views.py
from django.http import HttpResponse
from django.shortcuts import render
from django.utils.timezone import now,localtime
from .models import Article, Person, Author
# Create your views here.
def index(request):
# articles = Article(title='测试DateTimeField',create_time=now())
# articles.save()
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
# return HttpResponse("Success")
return render(request,'index.html',context={'create_time':create_time})
def email(request):
Person1 = Person()
Person1.email="359269746@qq.com"
Person1.save()
return HttpResponse("Success")
def null_text_field_view(request):
author=Author(username='zhiliao')
author.save()
return HttpResponse("Success")
def unique_view(request):
author=Author(username='aaa',telephone='111')
author.save()
return HttpResponse("Success")
urls.py
from django.urls import path
from article import views
urlpatterns = [
path("", views.index, name="index"),
path("email",views.email,name="email"),
path("null",views.null_text_field_view,name="null"),
path("unique",views.unique_view,name="unique"),
]
访问http://localhost:8000/unique
查看数据库保存了一个号码为111的数据
重新访问http://localhost:8000/unique，会报错。因为telephone的值是唯一的，如果再次插入数据会失败。
模型中meta配置
使用meta可以修改映射到数据库表的名字
models.py
from django.db import models
from django.utils.timezone import now
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Author(models.Model):
id = models.BigAutoField(primary_key=True)
username=models.CharField(max_length=100)
age=models.IntegerField(null=True,db_column='author_age',default=0)
create_time=models.DateTimeField(default=now)
telephone=models.CharField(max_length=11,unique=True,db_column='author_telephone',null=True)
修改models.py
from django.db import models
from django.utils.timezone import now
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Meta:
db_table='article'
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Meta:
db_table='person'
class Author(models.Model):
id = models.BigAutoField(primary_key=True)
username=models.CharField(max_length=100)
age=models.IntegerField(null=True,db_column='author_age',default=0)
create_time=models.DateTimeField(default=now)
telephone=models.CharField(max_length=11,unique=True,db_column='author_telephone',null=True)
class Meta:
db_table='author'
修改表的名字，查看数据库，可以看到数据库中的表名已经发生了改变
db_table：
这个配置映射到数据库中的表名，如果没有指定这个参数，那么在映射的时候将会使用模型名来作为默认的表名。
ordering：
设置在提取数据时的排序方式。
models.py
from django.db import models
from django.utils.timezone import now
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Meta:
db_table='article'
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Meta:
db_table='person'
class Author(models.Model):
id = models.BigAutoField(primary_key=True)
username=models.CharField(max_length=100)
age=models.IntegerField(null=True,db_column='author_age',default=0)
create_time=models.DateTimeField(default=now)
telephone=models.CharField(max_length=11,unique=True,db_column='author_telephone',null=True)
def __str__(self):
return "<(Author id:%s,create_time:%s)>" %(self.id,self.create_time)
class Meta:
db_table='author'
ordering=['username']
views.py
from django.http import HttpResponse
from django.shortcuts import render
from django.utils.timezone import now,localtime
from .models import Article, Person, Author
# Create your views here.
def index(request):
# articles = Article(title='测试DateTimeField',create_time=now())
# articles.save()
articles = Article.objects.get(pk=4)
create_time=articles.create_time
print(create_time)
localtime_now=localtime(articles.create_time)
print(localtime_now)
# return HttpResponse("Success")
return render(request,'index.html',context={'create_time':create_time})
def email(request):
Person1 = Person()
Person1.email="359269746@qq.com"
Person1.save()
return HttpResponse("Success")
def null_text_field_view(request):
author=Author(username='zhiliao')
author.save()
return HttpResponse("Success")
def unique_view(request):
author=Author(username='aaa',telephone='111')
author.save()
return HttpResponse("Success")
def get(request):
author=Author(username='aaa',telephone='111')
author.save()
return HttpResponse("Success")
def order_view(request):
authors=Author.objects.all()
for author in authors:
print(author)
return render(request,'result.html',context={'authors':authors})
urls.py
from django.urls import path
from article import views
urlpatterns = [
path("", views.index, name="index"),
path("email",views.email,name="email"),
path("null",views.null_text_field_view,name="null"),
path("unique",views.unique_view,name="unique"),
path("order_view",views.order_view,name="order_view"),
]
新建一个result.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
{% for author in authors %}
{{ author.id }}
{{ author.username }}
{{ author.age }}<br>
{% endfor %}
</body>
</html>
运行结果如下：
如果是反向排序
修改models.py
from django.db import models
from django.utils.timezone import now
# Create your models here.
class Article(models.Model):
# 如果想要使用自己定义的Field作为主键，那么必须要设置primary_key=True
id = models.BigAutoField(primary_key=True)
removed = models.BooleanField(default=False)
# title = models.CharField(max_length=255)
title = models.TextField()
# create_time=models.DateTimeField(auto_now_add=True)
create_time = models.DateTimeField(auto_now=True)
class Meta:
db_table='article'
class Person(models.Model):
id=models.BigAutoField(primary_key=True)
email=models.EmailField()
signature=models.TextField()
class Meta:
db_table='person'
class Author(models.Model):
id = models.BigAutoField(primary_key=True)
username=models.CharField(max_length=100)
age=models.IntegerField(null=True,db_column='author_age',default=0)
create_time=models.DateTimeField(default=now)
telephone=models.CharField(max_length=11,unique=True,db_column='author_telephone',null=True)
def __str__(self):
return "<(Author id:%s,create_time:%s)>" %(self.id,self.create_time)
class Meta:
db_table='author'
ordering=['-username']
访问http://localhost:8000/order_view
外键与表关系
外键：
在MySQL中，表有两种引擎，一种是InnoDB，另一种是myisam。如果使用的是InnoDB引擎，是支持外键约束的。外键的存在使得ORM框架在处理表关系的时候异常的强大。
类定义为class ForeignKey(tb，on_delete，**options)，第一个参数是引用的是哪个模型，第二个参数是在使用外键引用的模型数据被删除了，这个字段该如何处理，比如有CASCADE、SET_NULL等。
创建数据库orm_relationship_demo
create database orm_relationship_demo;
新建项目orm_relationship_demo，加入article app
在orm_relationship_demo的settings.py 的INSTALLED_APPS中加入app
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"article"
]
并在orm_relationship_demo的settings.py中配置数据库
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_relationship_demo',
# 数据库用户名
'USER': 'root',
# 数据库密码
'PASSWORD': '123456',
# 数据库的主机地址
'HOST': '127.0.0.1',
# 数据库端口号
'PORT': '3306'
}
}
article app模块下models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.CASCADE)
class Meta:
db_table = 'Article'
models.CASCADE实现外键关联
article表里面的category_id是category表中的id
配置article模块下的urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
]
配置orm_relationship_demo下的urls.py
from django.urls import path, include
urlpatterns = [
path("", include("article.urls")),
]
配置article app下的views.py
from django.http import HttpResponse
from .models import Article, Category
# Create your views here.
def index(request):
article = Article(title='abc',content='123')
category = Category(name='最新文章')
category.save()
article.category = category
article.save()
return HttpResponse("success")
运行结果如下：
查看数据库中表
引用其他app中的模型
如果想引用另一个app中的模型，使用app.模型的名字
创建一个front app
orm_relationship_demo.settings.py中加入front app模块
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"article",
"front"
]
front.models.py
from django.db import models
class FrontUser(models.Model):
username = models.CharField(max_length=100)
class Meta:
db_table = 'FrontUser'
将front的models映射到数据库中
更改article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.CASCADE)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
注意数据库frontuser表中的id类型为BIGINT，与article表中frontuser_id类型一致
引用自身为外键
新建一个comment表
article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.CASCADE)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
将comment映射到数据库中
查看数据库article_comment
外键映射自身comment_id
外键删除操作
如果一个模型使用了外键，那么在对方那个模型被删掉后，改进行什么样的操作，可以通过on_delete来指定，指定的类型如下：
1、CASCADE：级联操作。如果外键对应的那条数据被删除了，那么这条数据也会被删除。
2、PROTECT：受保护。即只要这条数据引用了外键的那条数据，那么就不能删除外键的那条数据。
3、SET_NULL：设置为空。如果外键的那条数据被删除了，那么在本条数据上就这个字段设置为空。如果设置了这个选项，前提是要指定这个字段可以为空。
4、SET_DEFAULT：设置默认值。如果外键的那条数据被删除了，那么本条数据上就这个字段设置为默认值。如果设置了这个选项，前提是要指定这个字段的一个默认值。
5、SET()：如果外键的那条数据被删除了，那么将会获取SET函数中的值来作为这个外键的值。SET函数可以接收一个可以调用的对象（比如函数或者方法），如果是可以调用的对象，那么会将这个对象调用后的结果作为值返回回去。
6、DO_NOTHING：不采用任何行为。一切全看数据库级别的约束
以上这些选项只是Django级别的，数据级别依旧是RESTRICT。
1、CASCADE：级联操作
查看article和category表中的数据
article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.CASCADE)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
这里使用的是category外键的级联删除。
article.urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
]
article.views.py
from django.http import HttpResponse
from .models import Article, Category
# Create your views here.
def index(request):
article = Article(title='abc',content='123')
category = Category(name='最新文章')
category.save()
article.category = category
article.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
orm_relationship_demo.urls.py
from django.urls import path, include
urlpatterns = [
path("", include("article.urls")),
]
settings.py
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_relationship_demo',
# 数据库用户名
'USER': 'root',
# 数据库密码
'PASSWORD': '123456',
# 数据库的主机地址
'HOST': '127.0.0.1',
# 数据库端口号
'PORT': '3306'
}
}
运行程序，删除id为1的category表中的数据
category表中的种类数据被删除，
article表中的数据也被删除
2、PROTECT：受保护
更改article.models.py，修改级联属性为PROTECT
models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.PROTECT)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
重新在数据库表中插入数据
访问http://localhost:8000/delete/5，当将属性改为protect时，无法级联删除数据。
3、SET_NULL：设置为空
更改article.models.py，修改级联属性为SET_NULL
models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.SET_NULL, null=True)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
重新映射数据库
删除前数据库中数据
删除category数据，访问http://localhost:8000/delete/5
重新查询数据库中的数据
4、SET_DEFAULT：设置默认值
article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.SET_DEFAULT, null=True, default=Category.objects.get(pk=7))
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
article.url.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
]
article.views.py
from django.http import HttpResponse
from .models import Article, Category
# Create your views here.
def index(request):
article = Article(title='abc',content='123')
category1 = Category(name='最新文章')
category2 = Category(name='默认分类')
category1.save()
category2.save()
article.category = category1
article.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
插入新的默认分类
当执行删除6默认分类时，
数据库表中的原始分类为6的数据分类会设置为7
5、SET()：
修改article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.SET(Category.objects.get(pk=7)), null=True)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
删除前数据库中表数据
访问http://localhost:8000/delete/8
删除后，article中数据category_id设置为7
6、DO_NOTHING：不采用任何行为
数据库表Category中存在restrict
article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.DO_NOTHING, null=True)
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
如果删除7，由于数据库中存在外键约束，不可以删除数据。
表关系
表之间的关系是通过外键进行关联的。而表之间的关系，无非就是三种关系：一对一、一对多（多对一）、多对多。
一对多
应用场景：比如文章和作者之间的关系，一个文章只能由一个作者编写，但是一个作者可以写多篇文章，文章和作者的关系就是典型的多对一的关系。
实现方式：一对多或者多对一，都是通过ForeignKey来实现的。
urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
path("one_to_many/",views.one_to_many,name="one_to_many"),
]
views.py
from django.http import HttpResponse
from front.models import FrontUser
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.frontuser= author
article.save()
return HttpResponse("success")
访问http://localhost:8000/one_to_many/
可以看到数据库中存在一对多的数据
如果想获取分类下所有的文章
models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
def __str__(self):
return self.name
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.DO_NOTHING, null=True)
frontuser = models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
articles.urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
path("one_to_many/",views.one_to_many,name="one_to_many"),
path("get_one_to_many/",views.get_one_to_many,name="get_one_to_many"),
]
views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.fontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.article_set.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
新建一个get_one_to_many.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
{% for article in articles %}
{{ article.title }}
{{ article.content }}
{{ article.category }}<br>
{% endfor %}
</body>
</html>
输出结果如下：http://localhost:8000/get_one_to_many/
或者使用related_name属性，修改models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
def __str__(self):
return self.name
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.DO_NOTHING, null=True,related_name='articles')
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
修改article.views.py，设置为category.[related_name属性值]
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.fontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
输出结果如下：http://localhost:8000/get_one_to_many/
如果设置分类后添加文章，views.py如下：
views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.fontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.fontuser = FrontUser.objects.first()
article.save()
category.articles.add(article)
category.save()
return HttpResponse("success")
urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
path("one_to_many/",views.one_to_many,name="one_to_many"),
path("get_one_to_many/",views.get_one_to_many,name="get_one_to_many"),
path("add_one_to_many/",views.add_one_to_many,name="add_one_to_many"),
]
运行http://localhost:8000/add_one_to_many/
查看数据库
如果category_id不可为空，且数据库中的设置不能为空,使用category.articles.add(article,bulk=False)会将所有的值都会插入数据库。就是说当数据库中不存在article的某条数据时，会先插入这条数据，然后修改此条数据的category_id
插入数据库前
models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
def __str__(self):
return self.name
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.DO_NOTHING, null=False,related_name='articles')
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.fontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
访问http://localhost:8000/add_one_to_many/
查看数据库中的值
一对一
1、应用场景：比如一个用户表和一个用户信息表，在实际网站中，可能需要保存用户的许多信息，但是有些信息是不常用的。如果把所有信息都存放到一张表中可能会影响查询效率，因此可以把用户的一些不常用的信息存放到另外一张表中我们叫做UserExtension，但是用户表User和用户信息表UserExtension就是典型的一对一了。
2、实现方式；Django为一对一提供了一个专门的Field叫做OneToOneField来实现一对一操作。
在front.models.py中加入FrontUserExtension，作为FrontUser扩展表，与FrontUser表一一对应
在Django中一对一是通过models.OneToOneField来实现的，这个OneToOneField其实本质上就是一个外键，只不过这个外键有一个唯一约束（unique key），如果想要反向引用，那么是通过引用模型的名字转换为小写的形式进行访问。
front.models.py
from django.db import models
class FrontUser(models.Model):
username = models.CharField(max_length=100)
class Meta:
db_table = 'FrontUser'
class FrontUserExtension(models.Model):
school=models.CharField(max_length=100)
user=models.OneToOneField("FrontUser", on_delete=models.CASCADE)
class Meta:
db_table = 'FrontUserExtension'
映射到数据库中
查看数据库
article.views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.fontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='qinghua')
extension.user=user
extension.save()
return HttpResponse("success")
article.urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
path("one_to_many/",views.one_to_many,name="one_to_many"),
path("get_one_to_many/",views.get_one_to_many,name="get_one_to_many"),
path("add_one_to_many/",views.add_one_to_many,name="add_one_to_many"),
path("add_one_to_one_view/",views.add_one_to_one_view,name="add_one_to_one_view"),
]
运行后访问http://localhost:8000/add_one_to_one_view/
查看数据库
如果更改插入数据
article.views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.fontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
访问http://localhost:8000/add_one_to_one_view/，由于是一对一关系，如果更改extension中的school重新插入会重新生成新的数据形成一对多，这种情况是不允许的。
根据frontuserextension的数据获取frontuser
front.models.py
from django.db import models
class FrontUser(models.Model):
username = models.CharField(max_length=100)
def __str__(self):
return "<FrontUser:(id %s, username %s)>" %(self.id, self.username)
class Meta:
db_table = 'FrontUser'
class FrontUserExtension(models.Model):
school=models.CharField(max_length=100)
user=models.OneToOneField("FrontUser", on_delete=models.CASCADE)
class Meta:
db_table = 'FrontUserExtension'
article.views.py 这是通过extension去获取user
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.frontuser = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser= FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
extension=FrontUserExtension.objects.first()
user=extension.user
return render(request, "get_one_to_one.html", context={"user": user, "extension":extension})
article.urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
path("one_to_many/",views.one_to_many,name="one_to_many"),
path("get_one_to_many/",views.get_one_to_many,name="get_one_to_many"),
path("add_one_to_many/",views.add_one_to_many,name="add_one_to_many"),
path("add_one_to_one_view/",views.add_one_to_one_view,name="add_one_to_one_view"),
path("get_one_to_one_view/",views.get_one_to_one_view,name="get_one_to_one_view"),
]
新建get_one_to_one.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
{{ user.id }}
{{ user.username }}
{{ extension.school }}
</body>
</html>
访问http://localhost:8000/get_one_to_one_view/
改写article.views.py 通过user去获取extension，user存在属性，属性值为扩展类FrontUserExtension的类名的小写frontuserextension
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.frontuser= author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.author = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
frontuser=FrontUser.objects.first()
extension=frontuser.frontuserextension
return render(request, "get_one_to_one.html", context={"user": frontuser, "extension":extension})
访问http://localhost:8000/get_one_to_one_view/
或者改写models.py，使用related_name属性
front.models.py
from django.db import models
class FrontUser(models.Model):
username = models.CharField(max_length=100)
def __str__(self):
return "<FrontUser:(id %s, username %s)>" %(self.id, self.username)
class Meta:
db_table = 'FrontUser'
class FrontUserExtension(models.Model):
school=models.CharField(max_length=100)
user=models.OneToOneField("FrontUser", on_delete=models.CASCADE,related_name='extension')
class Meta:
db_table = 'FrontUserExtension'
article.views.py，这里的extension=frontuser.extension就是models.py中的related_name
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.frontuser= author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.author = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
frontuser=FrontUser.objects.first()
extension=frontuser.extension
return render(request, "get_one_to_one.html", context={"user": frontuser, "extension":extension})
访问http://localhost:8000/get_one_to_one_view/
多对多
1、应用场景：比如文章和标签的关系，一篇文章可以有多个标签，一个标签可以被多个文章引用。因此标签和文章的关系是典型的多对多关系。
2、实现方式：Django为这种多对多的实现提供了专门的Field，叫做ManyToManyField，还是拿文章和标签为例进行讲解。示例代码如下：
article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
def __str__(self):
return self.name
class Tag(models.Model):
name = models.CharField(max_length=100)
articles=models.ManyToManyField("Article")
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.DO_NOTHING, null=False,related_name='articles')
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category, Tag
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.author = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
frontuser=FrontUser.objects.first()
extension=frontuser.extension
return render(request, "get_one_to_one.html", context={"user": frontuser, "extension":extension})
def add_many_to_many_view(request):
articles=Article.objects.first()
tag=Tag(name="热门文章")
tag.save()
articles.tag_set.add(tag)
return HttpResponse("success")
在多对多关系中，没有bulk参数，只能先save tag
数据库中映射表
articles.urls.py
from django.urls import path
from . import views
app_name = 'article'
urlpatterns = [
path("", views.index, name="index"),
path("delete/<int:id>", views.delete_view,name="delete_view"),
path("one_to_many/",views.one_to_many,name="one_to_many"),
path("get_one_to_many/",views.get_one_to_many,name="get_one_to_many"),
path("add_one_to_many/",views.add_one_to_many,name="add_one_to_many"),
path("add_one_to_one_view/",views.add_one_to_one_view,name="add_one_to_one_view"),
path("get_one_to_one_view/",views.get_one_to_one_view,name="get_one_to_one_view"),
path("add_many_to_many_view/", views.add_many_to_many_view, name="add_many_to_many_view"),
]
访问http://localhost:8000/add_many_to_many_view/
article表
article_tag表
article_tag_articles表
更改article.views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category, Tag
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.author = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
frontuser=FrontUser.objects.first()
extension=frontuser.extension
return render(request, "get_one_to_one.html", context={"user": frontuser, "extension":extension})
def add_many_to_many_view(request):
articles=Article.objects.first()
tag=Tag(name="冷门文章")
tag.save()
articles.tag_set.add(tag)
return HttpResponse("success")
article1同时拥有了两个tag
更改views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category, Tag
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.author = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
frontuser=FrontUser.objects.first()
extension=frontuser.extension
return render(request, "get_one_to_one.html", context={"user": frontuser, "extension":extension})
def add_many_to_many_view(request):
# articles=Article.objects.first()
# tag=Tag(name="冷门文章")
# tag.save()
# articles.tag_set.add(tag)
tag=Tag.objects.get(pk=3)
articles=Article.objects.get(pk=4)
tag.articles.add(articles)
return HttpResponse("success")
访问http://localhost:8000/add_many_to_many_view/
修改article.models.py
from django.db import models
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'Category'
def __str__(self):
return self.name
class Tag(models.Model):
name = models.CharField(max_length=100)
articles=models.ManyToManyField("Article",related_name="tags")
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey("Category", on_delete=models.DO_NOTHING, null=False,related_name='articles')
frontuser=models.ForeignKey("front.FrontUser",on_delete=models.CASCADE,null=True)
class Meta:
db_table = 'Article'
class Comment(models.Model):
content = models.TextField()
origin_comment = models.ForeignKey("self", on_delete=models.CASCADE)
views.py
from django.http import HttpResponse
from django.shortcuts import render
from front.models import FrontUser, FrontUserExtension
from .models import Article, Category, Tag
# Create your views here.
def index(request):
article1 = Article(title='zk的博客1',content='zk的博客')
article2 = Article(title='zk的博客2', content='zk的博客')
category1 = Category(pk=7)
category2 = Category(pk=9)
# category1.save()
# category2.save()
article1.category = category1
article1.save()
article2.category = category1
article2.save()
return HttpResponse("success")
def delete_view(request,id):
category = Category.objects.get(pk=id)
category.delete()
return HttpResponse("delete success")
def one_to_many(request):
article = Article(title='zk的博客3',content='zk的博客3')
category=Category.objects.first()
author=FrontUser.objects.first()
article.category = category
article.author = author
article.save()
return HttpResponse("success")
def get_one_to_many(request):
category = Category.objects.first()
articles=category.articles.all()
return render(request,"get_one_to_many.html",context={"articles":articles})
def add_one_to_many(request):
category = Category.objects.first()
article=Article(title='zk的博客4',content='zk的博客4')
article.frontuser = FrontUser.objects.first()
category.articles.add(article,bulk=False)
return HttpResponse("success")
def add_one_to_one_view(request):
user=FrontUser.objects.first()
extension=FrontUserExtension(school='zhiliao')
extension.user=user
extension.save()
return HttpResponse("success")
def get_one_to_one_view(request):
frontuser=FrontUser.objects.first()
extension=frontuser.extension
return render(request, "get_one_to_one.html", context={"user": frontuser, "extension":extension})
def add_many_to_many_view(request):
article=Article.objects.get(pk=1)
tags=article.tags
for tag in tags.all():
print(tag)
return HttpResponse("success")
可以看到打印的tags
ORM查询操作
查找是数据库操作中一个非常重要的技术，查询一般就是使用filter、exclude以及get三个方法来实现。我们可以在调用这些方法的时候传递不同的参数来实现查询需求，在ORM层面，这些查询条件都是使用field+—+condition的方式来使用的。以下将常用的查询条件来一一解释。
预置条件
学习ORM查询操作之前，需要做一些预置条件，示例代码如下：
新建项目orm_lookup_demo，使用Run manage.py Task
创建front app
在orm_lookup_demo.settings.py中加入front app
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"front"
]
更改orm_lookup_demo.settings.py中的数据库
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_lookup_demo',
# 数据库用户名
'USER': 'root',
# 数据库密码
'PASSWORD': '123456',
# 数据库的主机地址
'HOST': '127.0.0.1',
# 数据库端口号
'PORT': '3306'
}
}
在front app中创建front.models.py
from django.db import models
# Create your models here.
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
class Meta:
db_table = 'articles'
创建数据库
create database orm_lookup_demo;
映射到数据库中
pycharm连接数据库
打开数据库的表，添加两行数据
按下ctrl+enter键
pycharm中插入的数据就插入到数据库中了
然后在front.views.py中定义index视图
from django.http import HttpResponse
# Create your views here.
def index(request):
return HttpResponse("Hello, world. You're at the front index.")
在orm_lookup_demo.urls.py做映射
from django.urls import path
from front import views
urlpatterns = [
path("", views.index, name="index"),
]
查询条件
1、exact
exact：精确的提供条件，如果提供是一个None，那么在SQL层面就解释为NULL。精确查找
根据id精确查找
models.py
from django.db import models
# Create your models here.
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
def __str__(self):
return self.title+self.content
class Meta:
db_table = 'articles'
views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
article=Article.objects.filter(id__exact=2)
print(article)
return HttpResponse("Hello, world. You're at the front index.")
运行结果：
根据title精确查找
views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
article = Article.objects.filter(title__exact='钢铁是怎样炼成的')
print(article.query)
print(article)
return HttpResponse("Hello, world. You're at the front index.")
运行结果：
在windows操作系统上，mysql的排序规则（collection）无论是什么都是大小写不敏感的，在Linux操作系统上，mysql的排序规则（collection）从utf8_bin，那么就是大小写敏感的。
如果查询条件改为None
views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
# article=Article.objects.filter(id__exact=2)
article = Article.objects.filter(title__exact='None')
print(article.query)
print(article)
return HttpResponse("Hello, world. You're at the front index.")
输出结果为：
SQL语句是精确查找
2、iexact
更改views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
# article=Article.objects.filter(id__exact=2)
article = Article.objects.filter(content__iexact='bbb')
print(article.query)
print(article)
return HttpResponse("Hello, world. You're at the front index.")
访问http://localhost:8000/
SQL语句是模糊查找
3、get和filter的区别
QuerySet.query：query可以用来查看这个ORM查询语句最终被翻译成的SQL语句，但是query只能用在返回集QuerySet对象上，不能用在普通的ORM模型上，filter返回的是一个集合，QuerySet对象有query属性。
get方法只返回单个模型，ORM模型没有query属性。
更改views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
# article=Article.objects.filter(id__exact=2)
article1 = Article.objects.filter(content__iexact='bbb')
print(article1.query)
print(article1)
print(type(article1))
article2 = Article.objects.get(content__iexact='bbb')
print(article2)
print(type(article2))
return HttpResponse("Hello, world. You're at the front index.")
运行结果如下：http://localhost:8000/
4、contains
使用大小写敏感的判断。某个字符串是否在指定字段中，这个判断条件会使用大小写敏感，因此在被翻译成SQL语句的时候，会使用like binary，而like binary就是使用大小写敏感的。
更改views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
# article=Article.objects.filter(id__exact=2)
article1 = Article.objects.filter(content__iexact='bbb')
print(article1.query)
print(article1)
print(type(article1))
article2 = Article.objects.get(content__iexact='bbb')
print(article2)
print(type(article2))
return HttpResponse("Hello, world. You're at the front index.")
def index2(request):
article1 = Article.objects.filter(content__contains='bbb')
print(article1.query)
print(article1)
return HttpResponse("success")
orm_lookup_demo.urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index", views.index, name="index"),
path("index2", views.index2, name="index2"),
]
访问http://localhost:8000/index2
返回QuerySet匹配大小写的SQL QuerySet
如果更改views.py
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index(request):
# article=Article.objects.filter(id__exact=2)
article1 = Article.objects.filter(content__iexact='bbb')
print(article1.query)
print(article1)
print(type(article1))
article2 = Article.objects.get(content__iexact='bbb')
print(article2)
print(type(article2))
return HttpResponse("Hello, world. You're at the front index.")
def index2(request):
article1 = Article.objects.filter(content__contains='BBB')
print(article1.query)
print(article1)
return HttpResponse("success")
运行结果如下：
5、icontains
使用大小写不敏感的判断，某个字符串是否被包含在指定的字段中，这个查询语句在被翻译成SQL的时候，使用的是like，而like在MySQL层面就是不区分大小写的。
更改views文件如下：
from django.http import HttpResponse
from front.models import Article
# Create your views here.
def index2(request):
article1 = Article.objects.filter(content__contains='BBB')
print(article1.query)
print(article1)
article2 = Article.objects.filter(content__icontains='BBB')
print(article2.query)
print(article2)
return HttpResponse("success")
运行结果如下：
6、contains icontains和exact iexactd的区别
contains和icontains：在被翻译成SQL的时候使用的是%hello%，就是只要整个字符串中出现了'hello'都能被找到，而'exact'没有%，那么意味着只有完全相等的时候才会被匹配。
7、in
提取那些给定的field的值是否在容器中，容器可以是list，tuple或者任何一个可以迭代的对象，包括QuerySet对象。
可以直接指定某个字段的值时候在某个集合中，也可以通过其他的表的字段来判断是否在某个集合中。如果要判断相关联的表的字段，也是通过"__"来连接，并且在做反向引用的时候，不需要写"models_set"，直接使用模型的名字小写化就可以。比如通过分类去查找相应的文章，那么通过"article__id__in"就可以了，而非"article_set"形式。
单表查询
先在数据库中生成3条数据，ctrl+enter插入数据库
views.py
from django.http import HttpResponse
from front.models import Article
def index3(request):
articles = Article.objects.filter(id__in=[1,2,3])
for article in articles:
print(article)
return HttpResponse("success")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index3", views.index3, name="index3"),
]
访问http://localhost:8000/index3，运行结果如下：
多表查询
更改models.py
from django.db import models
# Create your models here.
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey(Category, on_delete=models.CASCADE,null=True)
def __str__(self):
return self.title+self.content
class Meta:
db_table = 'articles'
在category表中插入两条数据
然后更新articles表中category的数据
查找文章category_id为[1,2,3]的分类
views.py
from django.http import HttpResponse
from front.models import Article,Category
def index3(request):
categories = Category.objects.filter(article__id__in=[1, 2, 3])
for category in categories:
print(category)
return HttpResponse("success")
运行结果如下：http://localhost:8000/index3
如果不指定主键id，直接指定models类名，也会根据主键id去判断
更改views.py
from django.http import HttpResponse
from front.models import Article,Category
def index3(request):
# articles = Article.objects.filter(id__in=[1,2,3])
# for article in articles:
# print(article)
categories = Category.objects.filter(article__in=[1, 2, 3])
for category in categories:
print(category)
return HttpResponse("success")
运行结果如下：http://localhost:8000/index3
通过related_query_name查询，更改models.py
from django.db import models
# Create your models here.
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey(Category, on_delete=models.CASCADE,null=True,
related_query_name='articles')
def __str__(self):
return self.title+self.content
class Meta:
db_table = 'articles'
views.py
from django.http import HttpResponse
from front.models import Article,Category
def index3(request):
# articles = Article.objects.filter(id__in=[1,2,3])
# for article in articles:
# print(article)
# 注意此处articles__in与models.py中related_query_name值相同
categories = Category.objects.filter(articles__in=[1, 2, 3])
for category in categories:
print(category)
return HttpResponse("success")
访问http://localhost:8000/index3
因为在category的ForeignKey中指定了releated_query_name为article，因此你不能再使用article来进行反向查询了，这时候就需要通过article__id__in来进行反向查询。并且，如果在做反向查询的时候，如果查询的字段就是模型的主键，那么可以省略掉这个字段，直接写成article__in就可以了，不需要这个id。
查询文章中所有包含Hello的分类，将in的查询集变成QuerySet
views.py
from django.http import HttpResponse
from front.models import Article,Category
def index3(request):
articles=Article.objects.filter(title__icontains='hello')
category=Category.objects.filter(articles__in=articles)
print(category.query)
for category in category:
print(category)
return HttpResponse("success")
运行结果如下：
增加一行包含Hello的数据
相当于在SQL中构建了一个查询集，in不仅可以指定列表/元组，还可以为QuerySet。
8、gt、gte、lt、lte
gt：某个field的值要大于给定的值。
gte：某个field的值要大于等于给定的值。
lt：某个field的值要小于给定的值。
lte：某个field的值要小于等于给定的值。
views.py
from django.http import HttpResponse
from front.models import Article,Category
def index4(request):
articles_set1=Article.objects.filter(id__gt=2)
articles_set2 = Article.objects.filter(id__lt=2)
articles_set3 = Article.objects.filter(id__gte=2)
articles_set4 = Article.objects.filter(id__lte=2)
print(articles_set1.query)
print("articles_set1:")
for article1 in articles_set1:
print(article1)
print(articles_set2.query)
print("articles_set2:")
for article2 in articles_set2:
print(article2)
print(articles_set3.query)
print("articles_set3:")
for article3 in articles_set3:
print(article3)
print(articles_set4.query)
print("articles_set4:")
for article4 in articles_set4:
print(article4)
return HttpResponse("success")
orm_lookup_demo.urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index4", views.index4, name="index4"),
]
运行结果如下：
9、startswith和istartswith
startswith是判断某个字段的值是否是以某个值开始的，大小写敏感。
istartswith是判断某个字段的值是否是以某个值开始的，大小写不敏感。
views.py
from django.http import HttpResponse
from front.models import Article,Category
def index5(request):
articles_set1 = Article.objects.filter(title__startswith="Hello")
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("success")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index5", views.index5, name="index5"),
]
访问http://localhost:8000/index5
更改views.py
from django.http import HttpResponse
from front.models import Article,Category
def index5(request):
articles_set1 = Article.objects.filter(title__startswith="hello")
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("success")
访问http://localhost:8000/index5
查出来的是空值
更改views.py
from django.http import HttpResponse
from front.models import Article,Category
def index5(request):
articles_set1 = Article.objects.filter(title__istartswith="hello")
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("success")
运行结果如下：
10、endswith和iendswith
endswith是判断某个字段的值是否是以某个值结束的，大小写敏感。
iendswith是判断某个字段的值是否是以某个值结束的，大小写不敏感。
views.py
from django.http import HttpResponse
from front.models import Article,Category
def index5(request):
articles_set1 = Article.objects.filter(title__endswith="的")
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("success")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index5", views.index5, name="index5"),
]
http://localhost:8000/index5
更改views.py
from django.http import HttpResponse
from front.models import Article,Category
def index5(request):
articles_set1 = Article.objects.filter(title__iendswith="的")
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("success")
11、range
判断某个field的值是否在给定的区间中。时间查询条件，range可以指定一个时间段，并且时间应该标记为aware time。
更改models.py，在文章models中添加create_time
models.py
from django.db import models
# Create your models here.
class Category(models.Model):
name = models.CharField(max_length=100)
class Meta:
db_table = 'category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey(Category, on_delete=models.CASCADE,null=True,
related_query_name='articles')
create_time=models.DateTimeField(auto_now_add=True,null=True)
def __str__(self):
return self.title+self.content
class Meta:
db_table = 'articles'
更改settings.py中的时区设置
LANGUAGE_CODE = "en-us"
TIME_ZONE = "UTC"
USE_I18N = True
USE_TZ = True
更新数据表后可以看到数据库表中数据是UTC时间
views.py
from datetime import datetime
from django.http import HttpResponse
from front.models import Article,Category
def index6(request):
start_time=datetime(year=2024,month=7,day=21,hour=0,minute=0,second=0)
end_time=datetime(year=2024,month=7,day=22,hour=0,minute=0,second=0)
articles_set1 = Article.objects.filter(create_time__range=(start_time,end_time))
print(articles_set1.query)
print("articles_set1:")
for article1 in articles_set1:
print(article1)
return HttpResponse("index6")
orm_lookup_demo.url.py
from django.urls import path
from front import views
urlpatterns = [
path("index6", views.index6, name="index6")，
]
访问http://localhost:8000/index6
更改navie time为aware time
views.py
from datetime import datetime
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index6(request):
start_time = make_aware(datetime(year=2024, month=7, day=21, hour=0, minute=0, second=0))
end_time = make_aware(datetime(year=2024, month=7, day=22, hour=0, minute=0, second=0))
articles_set1 = Article.objects.filter(create_time__range=(start_time,end_time))
print(articles_set1.query)
print("articles_set1:")
for article1 in articles_set1:
print(article1)
return HttpResponse("index6")
运行结果如下：
12、date
针对某些date或者datetime类型的字段，可以指定date的范围，并且根据这个时间过滤，还可以使用链式调用。
需要copy时区文件到Mysql中，下载网址：https://dev.mysql.com/downloads/timezones.html
下载解压后，将文件拷贝到C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysql目录下
拷贝到此目录下后，重启mysql
views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index7(request):
articles_set1 = Article.objects.filter(create_time__date=datetime(year=2024, month=7, day=21))
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index7")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index7", views.index7, name="index7"),
]
13、year
根据年份进行查找。
views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index8(request):
articles_set1=Article.objects.filter(create_time__year=2024)
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index8")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index8", views.index8, name="index8"),
]
运行结果如下：
访问http://localhost:8000/index8
更改数据库中的年份
查找年份大于2022年的记录，更改views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index8(request):
articles_set1=Article.objects.filter(create_time__year__gt=2022)
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index8")
运行结果如下：http://localhost:8000/index8
查找年份大于等于2021年的记录，更改views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index8(request):
articles_set1=Article.objects.filter(create_time__year__gte=2022)
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index8")
运行结果如下：
14、month
同year，根据月份进行查找。
15、day
同year，根据日期进行查找。
16、weekday
更改数据库中的数据
更改views.py，查找创建时间为周一的书籍
views.py
def index8(request):
# articles_set1=Article.objects.filter(create_time__year__gt=2022)
articles_set1=Article.objects.filter(create_time__week_day=3)
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index8")
orm_lookup_demo.urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index8", views.index8, name="index8"),
]
访问http://localhost:8000/index8
17、time
range可以执行一个时间段，并且其时间应该标记为aware时间
views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index9(request):
starttime=time(hour=6, minute=10, second=27)
endtime=time(hour=23, minute=0, second=0)
articles_set1=Article.objects.filter(create_time__time__range=(starttime,endtime))
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index9")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index9", views.index9, name="index9"),
]
访问http://localhost:8000/index9
18、isnull
根据值是否为空进行查找。
将数据库中的数据其中两个值改为null
views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index10(request):
articles_set1 = Article.objects.filter(create_time__isnull=True)
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index10")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index10", views.index10, name="index10"),
]
访问http://localhost:8000/index10
19、regex和iregex
regex大小写敏感和iregex大小写不敏感的正则表达式。
views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index11(request):
articles_set1 = Article.objects.filter(title__regex=r'hello')
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index11")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index11", views.index11, name="index11"),
]
访问http://localhost:8000/index11
更改views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index11(request):
articles_set1 = Article.objects.filter(title__iregex=r'hello')
print(articles_set1.query)
for article1 in articles_set1:
print(article1)
return HttpResponse("index11")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index11", views.index11, name="index11"),
]
访问http://localhost:8000/index11
20、根据关联的表进行查询
加入现在有两个ORM模型，一个是Article，一个是Category
models.py
from django.db import models
# Create your models here.
class Category(models.Model):
name = models.CharField(max_length=100)
def __str__(self):
return self.name
class Meta:
db_table = 'category'
class Article(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
category = models.ForeignKey(Category, on_delete=models.CASCADE,null=True,
related_query_name='articles')
create_time=models.DateTimeField(auto_now_add=True,null=True)
def __str__(self):
return self.title+self.content
class Meta:
db_table = 'articles'
views.py
from datetime import datetime,time
from django.http import HttpResponse
from django.utils.timezone import make_aware
from front.models import Article,Category
def index12(request):
Categories=Category.objects.filter(articles__title__contains='Hello')
for category in Categories:
print(category)
return HttpResponse("success")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index12", views.index12, name="index12"),
]
使用关联查询，访问http://localhost:8000/index12
运行结果如下：
聚合函数
1、aggregate
1、所有的聚合函数都是放在'django.db.models'下面
2、聚合函数不能够单独执行，需要放在一些可以执行聚合函数的方法下面去执行。
3、聚合函数执行完成后，给这个聚合函数的值取个名字，取名字的规则，默认是“field+__+聚合函数的名字”形成的。如果不想使用默认的名字，那么可以在使用聚合函数的时候传递关键字参数进去，参数的名字就是聚合函数执行完成的名字。
4、“aggregate”：这个方法不会返回一个“QuerySet”对象，而是返回一个字典。这个字典中的key就是聚合函数的名字，值就是聚合函数执行后的结果。
aggregate的使用
创建数据库orm_aggregate_demo
create database orm_aggregate_demo;
创建项目orm_aggregate_demo，并加入front app
新建front.models.py
from django.db import models
class Author(models.Model):
"""作者模型"""
name = models.CharField(max_length=100)
age = models.IntegerField()
email = models.EmailField()
class Meta:
db_table = 'author'
class Publisher(models.Model):
"""出版社模型"""
name = models.CharField(max_length=300)
class Meta:
db_table = 'publisher'
class Book(models.Model):
"""图书模型"""
name = models.CharField(max_length=300)
pages = models.IntegerField()
price = models.FloatField()
rating = models.FloatField()
author = models.ForeignKey(Author,on_delete=models.CASCADE)
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
class Meta:
db_table = 'book'
class BookOrder(models.Model):
"""图书订单模型"""
book = models.ForeignKey("Book",on_delete=models.CASCADE)
price = models.FloatField()
class Meta:
db_table = 'book_order'
orm_aggregate_demo.settings.py
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_aggregate_demo',
# 数据库用户名
'USER': 'root',
# 数据库密码
'PASSWORD': '123456',
# 数据库的主机地址
'HOST': '127.0.0.1',
# 数据库端口号
'PORT': '3306'
}
}
加入front app
INSTALLED_APPS = [
"django.contrib.admin",
"django.contrib.auth",
"django.contrib.contenttypes",
"django.contrib.sessions",
"django.contrib.messages",
"django.contrib.staticfiles",
"front"
]
生成数据库表
在数据库中执行下列SQL，插入表和数据
use orm_aggregate_demo;
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for author
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`email` varchar(254) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO `author` VALUES ('1', '曹雪芹', '35', 'cxq@qq.com');
INSERT INTO `author` VALUES ('2', '吴承恩', '28', 'wce@qq.com');
INSERT INTO `author` VALUES ('3', '罗贯中', '36', 'lgz@qq.com');
INSERT INTO `author` VALUES ('4', '施耐庵', '46', 'sna@qq.com');
-- ----------------------------
-- Table structure for auth_group
-- ----------------------------
DROP TABLE IF EXISTS `auth_group`;
CREATE TABLE `auth_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_group
-- ----------------------------
-- ----------------------------
-- Table structure for auth_group_permissions
-- ----------------------------
DROP TABLE IF EXISTS `auth_group_permissions`;
CREATE TABLE `auth_group_permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_group_permissions_group_id_permission_id_0cd325b0_uniq` (`group_id`,`permission_id`),
KEY `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` (`permission_id`),
CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_group_permissions
-- ----------------------------
-- ----------------------------
-- Table structure for auth_permission
-- ----------------------------
DROP TABLE IF EXISTS `auth_permission`;
CREATE TABLE `auth_permission` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`content_type_id` int(11) NOT NULL,
`codename` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_permission_content_type_id_codename_01ab375a_uniq` (`content_type_id`,`codename`),
CONSTRAINT `auth_permission_content_type_id_2f476e4b_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_permission
-- ----------------------------
INSERT INTO `auth_permission` VALUES ('1', 'Can add log entry', '1', 'add_logentry');
INSERT INTO `auth_permission` VALUES ('2', 'Can change log entry', '1', 'change_logentry');
INSERT INTO `auth_permission` VALUES ('3', 'Can delete log entry', '1', 'delete_logentry');
INSERT INTO `auth_permission` VALUES ('4', 'Can add permission', '2', 'add_permission');
INSERT INTO `auth_permission` VALUES ('5', 'Can change permission', '2', 'change_permission');
INSERT INTO `auth_permission` VALUES ('6', 'Can delete permission', '2', 'delete_permission');
INSERT INTO `auth_permission` VALUES ('7', 'Can add group', '3', 'add_group');
INSERT INTO `auth_permission` VALUES ('8', 'Can change group', '3', 'change_group');
INSERT INTO `auth_permission` VALUES ('9', 'Can delete group', '3', 'delete_group');
INSERT INTO `auth_permission` VALUES ('10', 'Can add user', '4', 'add_user');
INSERT INTO `auth_permission` VALUES ('11', 'Can change user', '4', 'change_user');
INSERT INTO `auth_permission` VALUES ('12', 'Can delete user', '4', 'delete_user');
INSERT INTO `auth_permission` VALUES ('13', 'Can add content type', '5', 'add_contenttype');
INSERT INTO `auth_permission` VALUES ('14', 'Can change content type', '5', 'change_contenttype');
INSERT INTO `auth_permission` VALUES ('15', 'Can delete content type', '5', 'delete_contenttype');
INSERT INTO `auth_permission` VALUES ('16', 'Can add session', '6', 'add_session');
INSERT INTO `auth_permission` VALUES ('17', 'Can change session', '6', 'change_session');
INSERT INTO `auth_permission` VALUES ('18', 'Can delete session', '6', 'delete_session');
INSERT INTO `auth_permission` VALUES ('19', 'Can add author', '7', 'add_author');
INSERT INTO `auth_permission` VALUES ('20', 'Can change author', '7', 'change_author');
INSERT INTO `auth_permission` VALUES ('21', 'Can delete author', '7', 'delete_author');
INSERT INTO `auth_permission` VALUES ('22', 'Can add book', '8', 'add_book');
INSERT INTO `auth_permission` VALUES ('23', 'Can change book', '8', 'change_book');
INSERT INTO `auth_permission` VALUES ('24', 'Can delete book', '8', 'delete_book');
INSERT INTO `auth_permission` VALUES ('25', 'Can add book order', '9', 'add_bookorder');
INSERT INTO `auth_permission` VALUES ('26', 'Can change book order', '9', 'change_bookorder');
INSERT INTO `auth_permission` VALUES ('27', 'Can delete book order', '9', 'delete_bookorder');
INSERT INTO `auth_permission` VALUES ('28', 'Can add publisher', '10', 'add_publisher');
INSERT INTO `auth_permission` VALUES ('29', 'Can change publisher', '10', 'change_publisher');
INSERT INTO `auth_permission` VALUES ('30', 'Can delete publisher', '10', 'delete_publisher');
INSERT INTO `auth_permission` VALUES ('31', 'Can add store', '11', 'add_store');
INSERT INTO `auth_permission` VALUES ('32', 'Can change store', '11', 'change_store');
INSERT INTO `auth_permission` VALUES ('33', 'Can delete store', '11', 'delete_store');
-- ----------------------------
-- Table structure for auth_user
-- ----------------------------
DROP TABLE IF EXISTS `auth_user`;
CREATE TABLE `auth_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(128) NOT NULL,
`last_login` datetime(6) DEFAULT NULL,
`is_superuser` tinyint(1) NOT NULL,
`username` varchar(150) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(150) NOT NULL,
`email` varchar(254) NOT NULL,
`is_staff` tinyint(1) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`date_joined` datetime(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_user
-- ----------------------------
-- ----------------------------
-- Table structure for auth_user_groups
-- ----------------------------
DROP TABLE IF EXISTS `auth_user_groups`;
CREATE TABLE `auth_user_groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_user_groups_user_id_group_id_94350c0c_uniq` (`user_id`,`group_id`),
KEY `auth_user_groups_group_id_97559544_fk_auth_group_id` (`group_id`),
CONSTRAINT `auth_user_groups_group_id_97559544_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),
CONSTRAINT `auth_user_groups_user_id_6a12ed8b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_user_groups
-- ----------------------------
-- ----------------------------
-- Table structure for auth_user_user_permissions
-- ----------------------------
DROP TABLE IF EXISTS `auth_user_user_permissions`;
CREATE TABLE `auth_user_user_permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_user_user_permissions_user_id_permission_id_14a6b632_uniq` (`user_id`,`permission_id`),
KEY `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` (`permission_id`),
CONSTRAINT `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
CONSTRAINT `auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_user_user_permissions
-- ----------------------------
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) NOT NULL,
`pages` int(11) NOT NULL,
`price` double NOT NULL,
`rating` double NOT NULL,
`author_id` int(11) NOT NULL,
`publisher_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `book_author_id_c4d52965_fk_author_id` (`author_id`),
KEY `book_publisher_id_ac4b162a_fk_publisher_id` (`publisher_id`),
CONSTRAINT `book_author_id_c4d52965_fk_author_id` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`),
CONSTRAINT `book_publisher_id_ac4b162a_fk_publisher_id` FOREIGN KEY (`publisher_id`) REFERENCES `publisher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('1', '三国演义', '987', '98', '4.8', '3', '1');
INSERT INTO `book` VALUES ('2', '水浒传', '967', '97', '4.83', '4', '1');
INSERT INTO `book` VALUES ('3', '西游记', '1004', '95', '4.85', '2', '2');
INSERT INTO `book` VALUES ('4', '红楼梦', '1007', '99', '4.9', '1', '2');
-- ----------------------------
-- Table structure for book_order
-- ----------------------------
DROP TABLE IF EXISTS `book_order`;
CREATE TABLE `book_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`price` double NOT NULL,
`book_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `book_order_book_id_4178112d_fk_book_id` (`book_id`),
CONSTRAINT `book_order_book_id_4178112d_fk_book_id` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of book_order
-- ----------------------------
INSERT INTO `book_order` VALUES ('1', '95', '1');
INSERT INTO `book_order` VALUES ('2', '85', '1');
INSERT INTO `book_order` VALUES ('3', '88', '1');
INSERT INTO `book_order` VALUES ('4', '94', '2');
INSERT INTO `book_order` VALUES ('5', '93', '2');
-- ----------------------------
-- Table structure for django_admin_log
-- ----------------------------
DROP TABLE IF EXISTS `django_admin_log`;
CREATE TABLE `django_admin_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`action_time` datetime(6) NOT NULL,
`object_id` longtext,
`object_repr` varchar(200) NOT NULL,
`action_flag` smallint(5) unsigned NOT NULL,
`change_message` longtext NOT NULL,
`content_type_id` int(11) DEFAULT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `django_admin_log_content_type_id_c4bce8eb_fk_django_co` (`content_type_id`),
KEY `django_admin_log_user_id_c564eba6_fk` (`user_id`),
CONSTRAINT `django_admin_log_content_type_id_c4bce8eb_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),
CONSTRAINT `django_admin_log_user_id_c564eba6_fk` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_admin_log
-- ----------------------------
-- ----------------------------
-- Table structure for django_content_type
-- ----------------------------
DROP TABLE IF EXISTS `django_content_type`;
CREATE TABLE `django_content_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_label` varchar(100) NOT NULL,
`model` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_content_type
-- ----------------------------
INSERT INTO `django_content_type` VALUES ('1', 'admin', 'logentry');
INSERT INTO `django_content_type` VALUES ('3', 'auth', 'group');
INSERT INTO `django_content_type` VALUES ('2', 'auth', 'permission');
INSERT INTO `django_content_type` VALUES ('4', 'auth', 'user');
INSERT INTO `django_content_type` VALUES ('5', 'contenttypes', 'contenttype');
INSERT INTO `django_content_type` VALUES ('7', 'front', 'author');
INSERT INTO `django_content_type` VALUES ('8', 'front', 'book');
INSERT INTO `django_content_type` VALUES ('9', 'front', 'bookorder');
INSERT INTO `django_content_type` VALUES ('10', 'front', 'publisher');
INSERT INTO `django_content_type` VALUES ('11', 'front', 'store');
INSERT INTO `django_content_type` VALUES ('6', 'sessions', 'session');
-- ----------------------------
-- Table structure for django_migrations
-- ----------------------------
DROP TABLE IF EXISTS `django_migrations`;
CREATE TABLE `django_migrations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`applied` datetime(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_migrations
-- ----------------------------
INSERT INTO `django_migrations` VALUES ('1', 'contenttypes', '0001_initial', '2018-04-06 03:19:33.810505');
INSERT INTO `django_migrations` VALUES ('2', 'auth', '0001_initial', '2018-04-06 03:19:35.552138');
INSERT INTO `django_migrations` VALUES ('3', 'admin', '0001_initial', '2018-04-06 03:19:36.031414');
INSERT INTO `django_migrations` VALUES ('4', 'admin', '0002_logentry_remove_auto_add', '2018-04-06 03:19:36.044449');
INSERT INTO `django_migrations` VALUES ('5', 'contenttypes', '0002_remove_content_type_name', '2018-04-06 03:19:36.310155');
INSERT INTO `django_migrations` VALUES ('6', 'auth', '0002_alter_permission_name_max_length', '2018-04-06 03:19:36.544779');
INSERT INTO `django_migrations` VALUES ('7', 'auth', '0003_alter_user_email_max_length', '2018-04-06 03:19:36.788428');
INSERT INTO `django_migrations` VALUES ('8', 'auth', '0004_alter_user_username_opts', '2018-04-06 03:19:36.805473');
INSERT INTO `django_migrations` VALUES ('9', 'auth', '0005_alter_user_last_login_null', '2018-04-06 03:19:36.928803');
INSERT INTO `django_migrations` VALUES ('10', 'auth', '0006_require_contenttypes_0002', '2018-04-06 03:19:36.941837');
INSERT INTO `django_migrations` VALUES ('11', 'auth', '0007_alter_validators_add_error_messages', '2018-04-06 03:19:36.964897');
INSERT INTO `django_migrations` VALUES ('12', 'auth', '0008_alter_user_username_max_length', '2018-04-06 03:19:37.296779');
INSERT INTO `django_migrations` VALUES ('13', 'auth', '0009_alter_user_last_name_max_length', '2018-04-06 03:19:37.435148');
INSERT INTO `django_migrations` VALUES ('14', 'front', '0001_initial', '2018-04-06 03:19:38.896033');
INSERT INTO `django_migrations` VALUES ('15', 'sessions', '0001_initial', '2018-04-06 03:19:38.995337');
INSERT INTO `django_migrations` VALUES ('16', 'front', '0002_auto_20180406_1308', '2018-04-06 05:08:45.664059');
-- ----------------------------
-- Table structure for django_session
-- ----------------------------
DROP TABLE IF EXISTS `django_session`;
CREATE TABLE `django_session` (
`session_key` varchar(40) NOT NULL,
`session_data` longtext NOT NULL,
`expire_date` datetime(6) NOT NULL,
PRIMARY KEY (`session_key`),
KEY `django_session_expire_date_a5c62663` (`expire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_session
-- ----------------------------
-- ----------------------------
-- Table structure for publisher
-- ----------------------------
DROP TABLE IF EXISTS `publisher`;
CREATE TABLE `publisher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of publisher
-- ----------------------------
INSERT INTO `publisher` VALUES ('1', '中国邮电出版社');
INSERT INTO `publisher` VALUES ('2', '清华大学出版社');
1、Avg：求平均值
配置urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index1", views.index1, name="index1"),
]
views.py
from django.db import connection
from django.db.models import Avg
from django.shortcuts import render
from front.models import Book
# Create your views here.
def index1(request):
#获取所有图书的定价的平均价
result = Book.objects.aggregate(avg=Avg('price'))
print(result)
print(type(result))
print(connection.queries)
return render(request,"demo1.html",result)
这里传递了avg别名
在template下面新建一个demo1.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>demo1</title>
</head>
<body>
图书的平均值是：{{ avg }}
</body>
</html>
运行结果如下：http://localhost:8000/index1
数据库中表的数据如下：
控制台输出
aggregate和annotate的区别
1、aggregate：返回使用的聚合函数的字段和值
2、annotate：在原来的模型字段的基础之上添加一个使用了聚合函数的字段，并且在使用聚合函数的时候，会使用当前的这个模型的主键进行分组（group by）
aggregate和annotate的相同与不同：
1、相同：这两个方法都可以执行聚合函数
2、不同：
aggregate返回的是一个字典，在这个字典中存储的是这个聚合函数执行的结果。而annotate返回的是一个QuerySet对象，并且会在查找的模型上添加一个聚合函数的属性。
aggregate不会做分组，而annotate会使用group by子句进行分组，只有调用了group by子句，才能对每一条数据求聚合函数的值。
使用aggregate返回的是所有书的平均价格
views.py
from django.db import connection
from django.db.models import Avg
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book
def index2(request):
# 获取每一本书的平均价格
result=Book.objects.aggregate(avg=Avg('bookorder__price'))
print(result)
print(connection.queries)
return HttpResponse("index2")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index2", views.index2, name="index2"),
]
访问http://localhost:8000/index2，返回结果如下：
修改views.py
from django.db import connection
from django.db.models import Avg
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book
def index2(request):
# 获取每一本书的平均价格
results=Book.objects.annotate(avg=Avg('bookorder__price'))
print(results)
for result in results:
print(result.name,result.avg)
print(connection.queries)
return HttpResponse("index2")
运行后访问http://localhost:8000/index2
2、Count：获取指定对象的个数
views.py
from django.db import connection
from django.db.models import Avg, Count
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book
def index3(request):
# book表中总共有多少本书（book表中总共有多少id）
result = Book.objects.aggregate(book_nums=Count('id'))
print(result)
print(connection.queries)
return HttpResponse("index3")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index3", views.index3, name="index3"),
]
访问http://localhost:8000/index3
count可传递参数distinct，删除重复值
修改author表，改为两个相同的邮箱
更改views.py
from django.db import connection
from django.db.models import Avg, Count
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author
def index3(request):
# 统计作者表中有多少不同的邮箱
result = Author.objects.aggregate(email_count=Count('email',distinct=True))
print(result)
print(connection.queries)
return HttpResponse("index3")
输出结果：
统计每本书的销量，更改views.py
from django.db import connection
from django.db.models import Avg, Count
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author
def index3(request):
# 统计每本书的销量
books=Book.objects.annotate(book_nums=Count('bookorder'))
for book in books:
print('%s/%s',book.name,book.book_nums)
print(connection.queries)
return HttpResponse("index3")
访问http://localhost:8000/index3，输出结果：
3、Max、Min聚合函数
获取指定对象的最大值和最小值。
获取作者的最大年龄和最小年龄
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author
def index4(request):
result=Author.objects.aggregate(max=Max("age"),min=Min("age"))
print(result)
print(connection.queries)
return HttpResponse(result.get('max'))
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index4", views.index4, name="index4"),
]
访问http://localhost:8000/index4
获取一本图书的最大售卖价格和最小售卖价格
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index4(request):
result=Book.objects.annotate(max=Max("bookorder__price"),min=Min("bookorder__price"))
for result in result:
print("%s%s%s"%(result.name,result.max,result.min))
print(connection.queries)
return HttpResponse("index4")
访问http://localhost:8000/index4
4、sum函数
求某个字段值的总和。
aggregate和annotate方法可以在任何的QuerySet对象上调用，因此只要是返回了QuerySet对象，那么就可以进行链式调用。
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index5(request):
# 1、求所有图书的销售总额
result=BookOrder.objects.aggregate(total=Sum('price'))
print(result)
print(connection.queries)
return HttpResponse("index5")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index5", views.index5, name="index5"),
]
访问：http://localhost:8000/index5
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index5(request):
# 1、求各个图书的销售总额
results = Book.objects.annotate(total=Sum('bookorder__price'))
for result in results:
print(result.name,result.total)
print(connection.queries)
return HttpResponse("index5")
访问：http://localhost:8000/index5
求2024年年年度的每一本图书的销售总额
在models.py的BookOrder模型中添加create_time
class BookOrder(models.Model):
"""图书订单模型"""
book = models.ForeignKey("Book",on_delete=models.CASCADE)
price = models.FloatField()
create_time=models.DateTimeField(auto_now_add=True,null=True)
class Meta:
db_table = 'book_order'
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index6(request):
result=BookOrder.objects.filter(create_time__year=2024).aggregate(avg=Sum('price'))
print(result)
print(connection.queries)
return HttpResponse("index6")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index6", views.index6, name="index6"),
]
访问http://localhost:8000/index6
求每一本图书在2024年的销售总额
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index6(request):
results=Book.objects.filter(bookorder__create_time__year=2024).annotate(total=Sum('bookorder__price'))
for result in results:
print(result.name,result.total)
print(connection.queries)
return HttpResponse("index6")
输出结果：
F表达式和Q表达式
F表达式
F表达式：用来优化ORM操作数据库的。通常流程是首先从数据库中读取数据到Python内存中，然后在Python内存中做完运算，之后再保存到数据库中。而F表达式就可以优化这个流程，他可以不需要先把数据从数据库中提起出来，计算完在保存回去，他可以直接执行SQL语句。F表达式并不会马上从数据库中获取数据，而是在生成SQL语句的时候，动态的获取传递给F表达式的值。F表达式动态的获取某个字段上的值，并且这个F表达式，并不会真正的去数据库中查询数据，他相当于一个标识的作用。
view.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum,F
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index7(request):
# 给每一本图书的售价增加10元
results=Book.objects.update(price=F("price")+10)
print(connection.queries[-1])
return HttpResponse("index7")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index7", views.index7, name="index7"),
]
更新数据库数据之前
访问http://localhost:8000/index7
更新数据库数据之后
执行的SQL语句
获取作者表中，name和email相同的作者数据
更新下数据库author表
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum,F
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index7(request):
results=Author.objects.filter(name=F("email"))
for result in results:
print("%s,%s"%(result.name,result.email))
print(connection.queries[-1])
return HttpResponse("index7")
运行结果：http://localhost:8000/index7
Q表达式
使用Q表达式包裹查询条件，可以在条件之间进行多种操作，与或非等，从而实现一系列操作。
1、获取价格大于100，且评在4.85以上的图书
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum,F,Q
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index8(request):
books = Book.objects.filter(price__gte=100,rating__gte=4.85)
for book in books:
print("%s%s%s"%(book.name,book.rating,book.price))
print(connection.queries)
return HttpResponse("index8")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index8", views.index8, name="index8"),
]
访问http://localhost:8000/index8
使用Q表达式改写views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum,F,Q
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index8(request):
books = Book.objects.filter(Q(price__gte=100)&Q(rating__gte=4.85))
for book in books:
print("%s%s%s"%(book.name,book.rating,book.price))
print(connection.queries)
return HttpResponse("index8")
访问http://localhost:8000/index8
2、获取价格低于100元，或者评分低于4分的图书
修改book表数据
views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum,F,Q
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index8(request):
books = Book.objects.filter(Q(price__lt=100) | Q(rating__lt=4))
for book in books:
print("%s%s%s"%(book.name,book.rating,book.price))
print(connection.queries)
return HttpResponse("index8")
访问http://localhost:8000/index8
3、查找价格大于100，且标题中不包含'传'字的图书
更改views.py
from django.db import connection
from django.db.models import Avg, Count, Max, Min, Sum,F,Q
from django.http import HttpResponse
from django.shortcuts import render
from front.models import Book, Author, BookOrder
def index8(request):
books = Book.objects.filter(Q(price__gte=100) & ~Q(name__contains='传'))
for book in books:
print("%s%s%s"%(book.name,book.rating,book.price))
print(connection.queries)
return HttpResponse("index8")
访问http://localhost:8000/index8
QuertSet API
Objects对象的原理
我们通常做查询操作的时候，都是通过模型名.objects的方式进行操作，其实模型名.objects是一个django.db.models.manager.Manager对象，而django.db.models.manager.Manager这个类是一个"空壳"的类，它本身是没有任何的属性和方法的。他的方法全部都是通过Python动态添加的方式，从QuerySet类中拷贝过来的。总之objects方法都是从QuerySet类上copy过来的。
新建orm_queryset_demo项目
新建front app，添加相同的models和数据库表
front.models.py
from django.db import models
class Author(models.Model):
"""作者模型"""
name = models.CharField(max_length=100)
age = models.IntegerField()
email = models.EmailField()
class Meta:
db_table = 'author'
class Publisher(models.Model):
"""出版社模型"""
name = models.CharField(max_length=300)
class Meta:
db_table = 'publisher'
class Book(models.Model):
"""图书模型"""
name = models.CharField(max_length=300)
pages = models.IntegerField()
price = models.FloatField()
rating = models.FloatField()
author = models.ForeignKey(Author,on_delete=models.CASCADE)
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
class Meta:
db_table = 'book'
class BookOrder(models.Model):
"""图书订单模型"""
book = models.ForeignKey("Book",on_delete=models.CASCADE)
price = models.FloatField()
class Meta:
db_table = 'book_order'
orm_queryset_demo.settings.py
DATABASES = {
'default': {
# 数据库引擎:sqlite3/mysql/oracle
'ENGINE': 'django.db.backends.mysql',
# 数据库名
'NAME': 'orm_aggregate_demo',
# 数据库用户名
'USER': 'root',
# 数据库密码
'PASSWORD': '123456',
# 数据库的主机地址
'HOST': '127.0.0.1',
# 数据库端口号
'PORT': '3306'
}
}
front.views.py
from django.http import HttpResponse
from front.models import Book
from django.db.models.manager import Manager
# Create your views here.
def index(request):
print(type(Book.objects))
return HttpResponse("index")
orm_queryset_demo.urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index", views.index, name="index"),
]
运行访问http://localhost:8000/index
可以看到返回了manager对象
跟进manager对象，from django.db.models.manager import Manager
class Manager(BaseManager.from_queryset(QuerySet)):
pass
BaseManager.from_queryset(QuerySet)是manager的父类
@classmethod
def from_queryset(cls, queryset_class, class_name=None):
if class_name is None:
class_name = "%sFrom%s" % (cls.__name__, queryset_class.__name__)
return type(
class_name,
(cls,),
{
"_queryset_class": queryset_class,
**cls._get_queryset_methods(queryset_class),
},
)
1、class_name="BaseManagerFromQuerySet"
2、return type(
class_name,
(cls,),
{
"_queryset_class": queryset_class,
**cls._get_queryset_methods(queryset_class),
},
)
返回一个类，type函数可以动态的创建一个类，这里class_name类名为BaseManagerFromQuerySet。
(cls,)用来指定类的父类
**cls._get_queryset_methods(queryset_class)用来指定类的属性和方法。
_get_queryset_methods这个方法就是将QuerySet中的一些方法拷贝出来形成自己的方法。
返回新的QuerySet的方法
1、filter方法
在使用QuerySet进行查找操作的时候，可以提供多种操作，比如过滤完后还要根据某个字段进行排序，那么这一系列的操作我们可以通过一个非常流畅的链式调用的方式进行。
front.views.py
from django.db.models import Q
from django.http import HttpResponse
from front.models import Book
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
# Create your views here.
def index(request):
print(type(Book.objects))
return HttpResponse("index")
def index2(request):
books = Book.objects.filter(id__gte=2)
print(type(books))
books = books.filter(~Q(id=3))
for book in books:
print(book)
return HttpResponse("index2")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index2", views.index2, name="index2"),
]
访问http://localhost:8000/index2
可以看到这里filter方法返回了QuerySet对象。
改为链式调用
def index2(request):
books = Book.objects.filter(id__gte=2).filter(~Q(id=3))
for book in books:
print(book)
return HttpResponse("index2")
2、exclude方法
排除满足条件的数据，返回的是一个新的QuerySet。
views.py
from django.db.models import Q
from django.http import HttpResponse
from front.models import Book
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index3(request):
books = Book.objects.exclude(id=3)
for book in books:
print(book)
return HttpResponse("index3")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index3", views.index3, name="index3"),
]
访问http://localhost:8000/index3
访问的图书列表排除了id为3的结果
3、annotate方法
给QuerySet中的每个对象都添加一个使用查询表达式(聚合函数、F表达式、Q表达式、Func表达式)的新字段。
给图书添加作者名
views.py
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index4(request):
books = Book.objects.annotate(author_name=F("author__name"))
for book in books:
print("%s,%s"%(book.name,book.author.name))
print(connection.queries)
return HttpResponse("index4")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index4", views.index4, name="index4"),
]
这样会产生多条查询语句
更改views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index4(request):
books = Book.objects.annotate(author_name=F("author__name"))
for book in books:
print("%s,%s"%(book.name,book.author_name))
print(connection.queries)
return HttpResponse("index4")
这样只会产生一条查询语句
4、order_by
指定将查询结果根据某个字段进行排序，如果要倒序排序，那么可以在这个字段的前面加一个负号。
根据create_time进行从小到大进行排序
views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
orders=BookOrder.objects.order_by('create_time')
for order in orders:
print("%s,%s"%(order.id,order.create_time))
return HttpResponse("index5")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index5", views.index5, name="index5"),
]
访问http://localhost:8000/index5
根据create_time进行从大到小进行排序
更改views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
orders=BookOrder.objects.order_by('-create_time')
for order in orders:
print("%s,%s"%(order.id,order.create_time))
return HttpResponse("index5")
访问http://localhost:8000/index5
首先根据create_time进行排序，如果时间相同，则根据price进行排序
更改数据库中数据
views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
orders=BookOrder.objects.order_by('-create_time','-price')
for order in orders:
print("%s,%s,%s"%(order.id,order.create_time,order.price))
return HttpResponse("index5")
访问http://localhost:8000/index5
根据订单的图书的评分来进行排序（从小到大）
views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
orders=BookOrder.objects.order_by('book__rating')
for order in orders:
print("%s,%s,%s"%(order.id,order.book.rating,order.price))
return HttpResponse("index5")
运行结果：http://localhost:8000/index5
根据订单的图书的评分来进行排序（从大到小）
views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
orders=BookOrder.objects.order_by('-book__rating')
for order in orders:
print("%s,%s,%s"%(order.id,order.book.rating,order.price))
return HttpResponse("index5")
运行结果：
需要注意的一点，如果有多个order_by，会把前面的排序的规则全部打乱，而使用后面的排序方式：
views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
# Create your views here.
def index5(request):
orders=BookOrder.objects.order_by('-book__rating').order_by('price')
for order in orders:
print("%s,%s,%s"%(order.id,order.book.rating,order.price))
return HttpResponse("index5")
访问http://localhost:8000/index5
如果更改models.py中的类排序方式，就会默认使用models.py中的排序方式
from django.db import models
class Author(models.Model):
"""作者模型"""
name = models.CharField(max_length=100)
age = models.IntegerField()
email = models.EmailField()
class Meta:
db_table = 'author'
class Publisher(models.Model):
"""出版社模型"""
name = models.CharField(max_length=300)
class Meta:
db_table = 'publisher'
class Book(models.Model):
"""图书模型"""
name = models.CharField(max_length=300)
pages = models.IntegerField()
price = models.FloatField()
rating = models.FloatField()
author = models.ForeignKey(Author,on_delete=models.CASCADE)
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
class Meta:
db_table = 'book'
class BookOrder(models.Model):
"""图书订单模型"""
book = models.ForeignKey("Book",on_delete=models.CASCADE)
price = models.FloatField()
create_time=models.DateTimeField(auto_now_add=True)
class Meta:
db_table = 'book_order'
ordering=['-create_time','price']
views.py
from django.db import connection
from django.db.models import Q, F
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
orders=BookOrder.objects.all()
for order in orders:
print("%s,%s,%s"%(order.id,order.book.rating,order.price))
return HttpResponse("index5")
访问：http://localhost:8000/index5
提取图书数据，根据图书销量进行排序（从大到小进行排序）
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index5(request):
books = Book.objects.annotate(order_nums=Count("bookorder")).order_by("-order_nums")
for book in books:
print("%s,%s"%(book.name,book.order_nums))
return HttpResponse("index5")
访问http://localhost:8000/index5
5、values用法
用来指定在提取数据出来，需要提取哪些字段，默认情况下会把表中所有的字段全部提取出来，可以使用values来进行指定，并且使用了values方法，提取出的QuerySet中的数据类型不是模型，而是values指定的字段和值形成的字典。有时候我们在表中查找数据的时候，并不是想要把所有的字段都提取出来，我们有可能只想要其中的几个字段，这时候就可以使用values来实现，需要什么字段，就把这个字段名字传到这个方法中。values返回值同样也是一个QuerySet对象，但是这个QuerySet中装的就不再是模型了，而是字典。
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index6(request):
books=Book.objects.values("id","name")
print(type(books))
for book in books:
print("%s,%s"%(book["name"],book["id"]))
return HttpResponse("index6")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index6", views.index6, name="index6"),
]
访问：http://localhost:8000/index6
如果我们想要提取这个模型上关联的对象的属性那么也是可以的，查找顺序与filter的用法是一样的。
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
def index6(request):
books=Book.objects.values("id","name","author__name")
print(type(books))
for book in books:
print("%s,%s,%s"%(book["id"],book["name"],book["author__name"]))
return HttpResponse("index6")
运行结果如下：
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
def index6(request):
books=Book.objects.values("id","name",author_name=F("author__name"))
print(type(books))
for book in books:
print("%s,%s,%s"%(book["id"],book["name"],book["author_name"]))
return HttpResponse("index6")
运行结果如下：
自定义的名字，不能和模型上本身拥有的字段一样。
在values中，也可以使用聚合函数形成一个新的字段
获取图书的销量
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index6(request):
books=Book.objects.values("id","name",order_nums=Count("bookorder"))
print(type(books))
for book in books:
print("%s,%s,%s"%(book["id"],book["name"],book["order_nums"]))
return HttpResponse("index6")
访问http://localhost:8000/index6
如果调用values方法的时候没有传递任何的参数，那么会获取这个模型上所有的字段以及对应的值形成的字典
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index6(request):
books=Book.objects.values()
for book in books:
print(book)
return HttpResponse("index6")
运行结果如下：提取出来book中所有的值
6、values_list
跟values一样的作用，只不过这个方法返回的QuerySet中，装的不是字典，而是元组
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index6(request):
books=Book.objects.values_list()
for book in books:
print(book)
return HttpResponse("index6")
运行结果如下：
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index6(request):
books=Book.objects.values_list("name")
for book in books:
print(book)
return HttpResponse("index6")
这样只会返回单个字段
如果设置flat=True，会拆分元组，但是values_list中只能存放一个字段
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index6(request):
books=Book.objects.values_list("name",flat=True)
for book in books:
print(book)
return HttpResponse("index6")
返回结果如下：
7、all
获取这个ORM模型的QuerySet对象。这个QuerySet对象没有经过任何的修改。
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
def index7(request):
books=Book.objects.all()
print(books)
for book in books:
print(book.name)
return HttpResponse("index7")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index7", views.index7, name="index7"),
]
访问http://localhost:8000/index7
8、select_related
在提取某个模型的数据的同时，也提前将相关联的数据提取出来，比如提取文章数据，可以使用select_related将author信息提取出来，以后再次使用article.author的时候就不需要再次去访问数据库了，可以减少数据库查询次数。注意这个方法只能用在外键的关联对象上，对于那种多对多，或者是多对一的情况，不能使用他来实现，而应该使用prefetch_related来实现。
常规查询方法：
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index8(request):
books=Book.objects.all()
print(books)
for book in books:
print(book.author.name)
print(connection.queries)
return HttpResponse("index8")
访问http://localhost:8000/index8
更改使用select_related
views.py
def index8(request):
books = Book.objects.select_related("author")
print(books)
for book in books:
print(book.author.name)
print(connection.queries)
return HttpResponse("index8")
运行结果如下：
views.py
def index8(request): books = Book.objects.select_related("author","publisher") print(books) for book in books: print(book.author.name) print(book.publisher.name) print(connection.queries) return HttpResponse("index8")
运行结果如下：
9、prefetch_related
这个方法和selected_related非常的相似，就是在访问多个表中的数据的时候，减少查询的次数。这个方法是为了解决多对一和多对多的关系的查询问题，会产生两个查询语句，所以，如果在这个方法中查询使用外键关联的模型的时候，也会产生两个查询语句，因此如果是外键关联的模型，建议使用select_related方法。
需要注意的是，在使用prefetch_related查找出来的bookorder_set，建议不要再对他进行任何操作，比如filter，不然又会产生N多查询语句。
比如要获取标题中带有hello字符串的文章以及他的所有标签
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index9(request):
Books=Book.objects.prefetch_related("bookorder_set")
for book in Books:
print("="*30)
print(book.name)
orders=book.bookorder_set.all()
for order in orders:
print(order.id)
print(connection.queries)
return HttpResponse("index9")
urls.py
from django.urls import path
from front import views
urlpatterns = [
path("index9", views.index9, name="index9"),
]
访问http://localhost:8000/index9
使用原始查询方法，需要进行多次查询
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index9(request):
Books=Book.objects.all()
for book in Books:
print("="*30)
print(book.name)
orders=book.bookorder_set.all()
for order in orders:
print(order.id)
print(connection.queries)
return HttpResponse("index9")
访问http://localhost:8000/index9
使用prefetch_related查询外键关联信息
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index9(request):
Books=Book.objects.prefetch_related("author")
for book in Books:
print(book.author.name)
print(connection.queries)
return HttpResponse("index9")
运行结果：
查询价格大于90元的书籍
views.py
from django.db import connection
from django.db.models import Q, F, Count
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index9(request):
Books=Book.objects.prefetch_related("bookorder_set")
for book in Books:
orders = book.bookorder_set.filter(price__gte=90)
for order in orders:
print(order.id)
print(connection.queries)
return HttpResponse("index9")
访问http://localhost:8000/index9
使用预查询的方式查找价格大于90的书籍
from django.db import connection
from django.db.models import Q, F, Count,Prefetch
from django.http import HttpResponse
from front.models import Book,Author,BookOrder
from django.db.models.manager import Manager
from django.db.models.query import QuerySet
def index9(request):
prefetch = Prefetch("bookorder_set",queryset=BookOrder.objects.filter(price__gte=90))
Books=Book.objects.prefetch_related(prefetch)
for book in Books:
orders = book.bookorder_set.all()
for order in orders:
print(order.id)
print(book.author.name+"\t"+book.publisher.name)
print(connection.queries)
return HttpResponse("index9")
输出结果如下：
浙公网安备 33010602011771号