目录
- Django中如何开启事务
- orm中常用字段及参数
- 数据库查询优化
1、Django中如何开启事务
事务的ACID四个特性:
原子性(Atomicity):不可分割的最小单位
一致性(Consistency):跟原子性相辅相成
隔离性(Isolation):事务之间互相不干扰
持久性(Durability):事务一旦确认永久生效
事务的回滚rollback
事务的确认commit
数据库三大设计范式
待完善……
如何开启事务
import os
if __name__ == "__main__":
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day64.settings")
import django
django.setup()
from app01 import models
models.User.objects.all()
# 开启事务
from django.db import transaction
try:
with transaction.atomic():
# sql1
# sql2
# 在with代码块内书写的所有orm操作都属于同一个事务
except Exception as e:
print(e)
print('other operation')
2、orm中常用字段及参数
AutoField(Field) - int自增列,必须填入参数 primary_key=True
BigAutoField(AutoField) - bigint自增列,必须填入参数 primary_key=True
SmallIntegerField(IntegerField): - 小整数 -32768 ~ 32767
PositiveSmallIntegerField(PositiveIntegerRelDbTypeMixin, IntegerField) - 正小整数 0 ~ 32767
IntegerField(Field) - 整数列(有符号的) -2147483648 ~ 2147483647
PositiveIntegerField(PositiveIntegerRelDbTypeMixin, IntegerField) - 正整数 0 ~ 2147483647
BigIntegerField(IntegerField): - 长整型(有符号的) -9223372036854775808 ~ 9223372036854775807
BooleanField(Field) - 布尔值类型
该字段传布尔值(False/True),数据库里面存0或1
NullBooleanField(Field): - 可以为空的布尔值
CharField(Field) - 字符类型
- 必须提供max_length参数, max_length表示字符长度
TextField(Field) - 文本类型
-该字段可以用来存大段内容(文章、博客),没有字数限制
EmailField(CharField): - 字符串类型,Django Admin以及ModelForm中提供验证机制,本质上是varchar(254)
IPAddressField(Field) - 字符串类型,Django Admin以及ModelForm中提供验证 IPV4 机制
GenericIPAddressField(Field) - 字符串类型,Django Admin以及ModelForm中提供验证 Ipv4和Ipv6
- 参数:
protocol,用于指定Ipv4或Ipv6, 'both',"ipv4","ipv6"
unpack_ipv4, 如果指定为True,则输入::ffff:192.0.2.1时候,可解析为192.0.2.1,开启刺功能,需要protocol="both"
URLField(CharField) - 字符串类型,Django Admin以及ModelForm中提供验证 URL
SlugField(CharField) - 字符串类型,Django Admin以及ModelForm中提供验证支持 字母、数字、下划线、连接符(减号)
CommaSeparatedIntegerField(CharField) - 字符串类型,格式必须为逗号分割的数字
UUIDField(Field) - 字符串类型,Django Admin以及ModelForm中提供对UUID格式的验证
FilePathField(Field) - 字符串,Django Admin以及ModelForm中提供读取文件夹下文件的功能
- 参数:
path, 文件夹路径
match=None, 正则匹配
recursive=False, 递归下面的文件夹
allow_files=True, 允许文件
allow_folders=False, 允许文件夹
FileField(Field) - 字符串,路径保存在数据库,文件上传到指定目录
- 参数:
upload_to = "" 上传文件的保存路径,upload_to="/data",给该字段传一个文件对象,会自动将文件保存到/data目录下,然后将文件路径保存到数据库中。
storage = None 存储组件,默认django.core.files.storage.FileSystemStorage
ImageField(FileField) - 字符串,路径保存在数据库,文件上传到指定目录
- 参数:
upload_to = "" 上传文件的保存路径
storage = None 存储组件,默认django.core.files.storage.FileSystemStorage
width_field=None, 上传图片的高度保存的数据库字段名(字符串)
height_field=None 上传图片的宽度保存的数据库字段名(字符串)
DateTimeField(DateField) - 日期+时间格式 YYYY-MM-DD HH:MM[:ss[.uuuuuu]][TZ]
DateField(DateTimeCheckMixin, Field) - 日期格式 YYYY-MM-DD
TimeField(DateTimeCheckMixin, Field) - 时间格式 HH:MM[:ss[.uuuuuu]]
- 参数:
auto_now:每次修改数据的时候都会自动更新当前时间
auto_nowadd:只在创建数据的时候记录创建时间后续不会自动修改了
DurationField(Field) - 长整数,时间间隔,数据库中按照bigint存储,ORM中获取的值为datetime.timedelta类型
FloatField(Field) - 浮点型
DecimalField(Field) - 10进制小数
- 参数:
max_digits,小数总长度
decimal_places,小数位长度
BinaryField(Field)- 二进制类型
外键字段及参数:
ForeignKey(unique=True) 等价于 OneToOneField()
![db16ba4786b0beb6a35c7e50e2802048.png](https://i-blog.csdnimg.cn/blog_migrate/6d3b70cc854d4e55443e7fec4fe2c324.jpeg)
![54e678822de7e60d9a7efc9d458bb8d8.png](https://i-blog.csdnimg.cn/blog_migrate/e46c48c1ebf6b130c0293b291de57bd3.jpeg)
Django除了给你提供很多字段之外,还支持自定义字段
# 自定义字段类型
class MyCharField(models.Field):
def __init__(self,max_length,*args,**kwargs):
self.max_length = max_length
# 调用父类的init方法
super().__init__(max_length=max_length,*args,**kwargs) # 一定要是关键字的形式传入
def db_type(self, connection):
"""
返回真正的数据类型及各种约束条件
:param connection:
:return:
"""
return 'char(%s)'%self.max_length
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=8,decimal_places=2)
publish_date = models.DateField(auto_now_add=True)
# 库存
inventory = models.IntegerField(default=1000)
# 卖出
sale = models.IntegerField(default=1000)
# 自定义字段类型使用
MyField = MyCharField(max_length=16,null=True)
# 书籍与出版社一对多的关系:
publish = models.ForeignKey(to='Publish')
# 书籍与作者多对多关系:
authors = models.ManyToManyField(to='Author')
![a760480c7c264ec3b1c25c51b4ba7c37.png](https://i-blog.csdnimg.cn/blog_migrate/13fead1ba1f5226ff00367e3c0683793.png)
模型层里面1.x外键默认都是级联更新删除
但是2.x和3.x中需要手动配置参数
1.x:
models.ForeignKey(to='Publish')
2.x和3.x:
models.ForeignKey(to='Publish',on_delete = models.CASCADE())
3、数据库查询优化
only与defer
select_related与prefetch_related
orm语句的特点:
惰性查询:如果你仅仅只是书写了orm语句,在后面根本没有用到该语句所查询出来的参数,就不会执行查询,不会走数据库;要用到数据了才会走数据库。
only与defer区别
import os
if __name__ == "__main__":
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day64.settings")
import django
django.setup()
from app01 import models
# res = models.User.objects.all()
# print(res)
'''
执行的SQL语句:
(0.001) SELECT `app01_user`.`id`, `app01_user`.`name`, `app01_user`.`age`, `app01_user`.`register_time` FROM `app01_user` LIMIT 21; args=()
结果为:
<QuerySet [<User: 对象:jack>, <User: 对象:nimoding>, <User: 对象:jack_sam>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:ross>, <User: 对象:nimo>, <User: 对象:jack>, <User: 对象:ross1>, <User: 对象:ross2>]>
'''
# 想要获取书籍表中所有书的名字
# res = models.Book.objects.values('title')
# print(res)
'''
执行的SQL语句:
(0.001) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SELECT `app01_book`.`title` FROM `app01_book` LIMIT 21; args=()
结果为:
<QuerySet [{'title': '三国演义(爆款)'}, {'title': '红楼梦(爆款)'}, {'title': '三国演义(爆款)'}, {'title': '红楼梦(爆款)'}, {'title': '三国演义(爆款)'}, {'title': '红楼梦(爆款)'}, {'title': '三国演义(爆款)'}, {'title': '红楼梦(爆款)'}, {'title': '三国演义(爆款)'}, {'title': '红楼梦(爆款)'}, {'title': '你今天真好看(爆款)'}, {'title': '人性的弱点(爆款)'}, {'title': '次第花开(爆款)'}]>
拿到的是类似列表中套字典的格式数据,并不是只包含书籍的名字。
'''
# for i in res:
# # print(i['title'])
# print(i.get('title'))
'''
需要通过一个for循环才能真正拿到只有书籍名称的数据:
SQL语句为:
(0.002) SELECT `app01_book`.`title` FROM `app01_book`; args=()
结果为:
红楼梦(爆款)
三国演义(爆款)
红楼梦(爆款)
你今天真好看(爆款)
人性的弱点(爆款)
次第花开(爆款)
'''
# 进一步要求实现的功能:获取到一个数据对象,然后.title就能够拿到书名这一个字段
# only测试:
# res = models.Book.objects.only('title')
# print(res)
'''
SQL语句:
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title` FROM `app01_book` LIMIT 21; args=()
结果为:
<QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>]>
这里拿到的是一个对象
'''
# 既然res拿到的是对象,那我们可以通过for循环拿到title字段
# for i in res:
# print(i.title) # 点only括号内的字段,不会走数据库
'''
SQL语句:
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title` FROM `app01_book`; args=()
结果为:
红楼梦(爆款)
你今天真好看(爆款)
人性的弱点(爆款)
次第花开(爆款)
'''
# for i in res:
# print(i.price) # 点only括号内没有的字段,会重新走数据库查询
'''
SQL和结果:(没获取一条数据都会走一下数据库)
373.12
473.12
(0.000) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 8; args=(8,)
(0.000) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 9; args=(9,)
373.12
(0.001) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 10; args=(10,)
(0.002) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 11; args=(11,)
473.12
(0.001) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 13; args=(13,)
273.12
314.12
(0.000) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 14; args=(14,)
295.12
(0.001) SELECT `app01_book`.`id`, `app01_book`.`price` FROM `app01_book` WHERE `app01_book`.`id` = 15; args=(15,)
'''
# res = models.Book.objects.all() 不需要走数据库查询
# defer测试
res = models.Book.objects.defer('title')
# print(res)
'''
SQL语句:
(0.001) SELECT `app01_book`.`id`, `app01_book`.`price`, `app01_book`.`publish_date`, `app01_book`.`inventory`, `app01_book`.`sale`, `app01_book`.`MyField`, `app01_book`.`publish_id` FROM `app01_book` LIMIT 21; args=()
结果为:
<QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>]>
'''
# for i in res:
# print(i.price)
'''
SQL语句和结果:
(0.001) SELECT `app01_book`.`id`, `app01_book`.`price`, `app01_book`.`publish_date`, `app01_book`.`inventory`, `app01_book`.`sale`, `app01_book`.`MyField`, `app01_book`.`publish_id` FROM `app01_book`; args=()
373.12
473.12
373.12
473.12
373.12
'''
for i in res:
print(i.title) # 每次循环都会走数据库
'''
SQL语句和结果:
红楼梦(爆款)
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title` FROM `app01_book` WHERE `app01_book`.`id` = 11; args=(11,)
你今天真好看(爆款)
人性的弱点(爆款)
(0.000) SELECT `app01_book`.`id`, `app01_book`.`title` FROM `app01_book` WHERE `app01_book`.`id` = 13; args=(13,)
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title` FROM `app01_book` WHERE `app01_book`.`id` = 14; args=(14,)
次第花开(爆款)
(0.001) SELECT `app01_book`.`id`, `app01_book`.`title` FROM `app01_book` WHERE `app01_book`.`id` = 15; args=(15,)
'''
# 总结:defer和only刚好相反,查询defer括号内放的字段,需要重新走数据库,查询非defer括号内的字段则不需要走数据库。
select_related与prefetch_related区别
import os
if __name__ == "__main__":
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day64.settings")
import django
django.setup()
from app01 import models
models.User.objects.all()
# select_related与prefetch_related都跟跨表操作有关
# 两者对用户来讲没有什么差距但是两者本质不一样:
# select_related是inner join联表操作,prefetch_related是子查询
# res = models.Book.objects.all()
# for i in res:
# print(i.publish.name)
'''
没拿一次数据都会走一次数据库
SQL语句和结果:
东方出版社
人民出版社
人民出版社
东方出版社
东方出版社
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` = 1; args=(1,)
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` = 2; args=(2,)
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` = 2; args=(2,)
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` = 1; args=(1,)
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` = 1; args=(1,)
'''
# 接下来我们用select_related测试:
res = models.Book.objects.select_related('publish') # INNER JOIN
# print(res)
'''
SQL语句和结果:
(0.001) SELECT `app01_book`.`id`,
`app01_book`.`title`,
`app01_book`.`price`,
`app01_book`.`publish_date`,
`app01_book`.`inventory`,
`app01_book`.`sale`,
`app01_book`.`MyField`,
`app01_book`.`publish_id`,
`app01_publish`.`id`,
`app01_publish`.`name`,
`app01_publish`.`addr`,
`app01_publish`.`email`
FROM `app01_book` INNER JOIN `app01_publish`
ON (`app01_book`.`publish_id` = `app01_publish`.`id`)
LIMIT 21; args=()
<QuerySet [<Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>]>
'''
# for i in res:
# print(i.publish.name)
'''
SQL语句和结果:(跟直接print(res)效果一样)
(0.002) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`publish_date`, `app01_book`.`inventory`, `app01_book`.`sale`, `app01_book`.`MyField`, `app01_book`.`publish_id`, `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`); args=()
东方出版社
人民出版社
人民出版社
东方出版社
东方出版社
'''
# select_related内部直接先将book表和publish表连起来,然后一次性将大表里面的所有数据全部封装给查询出来的对象
# 这时无论是.book数据还是.publish数据都无需再走数据库查询了。
# 注意select_related括号内只能放外键字段(可以放一对多、一对一,不支持多对多)
# 可以是这种形式select_related(外键字段1__外键字段2__外键字段3……)
# prefetch_related测试:
res = models.Book.objects.prefetch_related('publish')
for i in res:
print(i.publish.name)
'''
SQL语句和结果:
这里运行了两条SQL语句
(0.002) SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`price`, `app01_book`.`publish_date`, `app01_book`.`inventory`, `app01_book`.`sale`, `app01_book`.`MyField`, `app01_book`.`publish_id` FROM `app01_book`; args=()
(0.001) SELECT `app01_publish`.`id`, `app01_publish`.`name`, `app01_publish`.`addr`, `app01_publish`.`email` FROM `app01_publish` WHERE `app01_publish`.`id` IN (1, 2); args=(1, 2)
人民出版社
人民出版社
东方出版社
人民出版社
'''
# prefetch_related该方法内部其实就是子查询,将子查询查询出来的所有结果也封装到了对象中。