plsql大字段保存类型_Day65 Django 开启事务/orm常用字段

目录

  • 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()

Django ORM中常用字段和参数​www.cnblogs.com
db16ba4786b0beb6a35c7e50e2802048.png
菲宇:一文读懂|Django之Model操作数据库详解​zhuanlan.zhihu.com
54e678822de7e60d9a7efc9d458bb8d8.png

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
模型层里面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该方法内部其实就是子查询,将子查询查询出来的所有结果也封装到了对象中。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值