django中的ORM

ORM

配置测试文件

去manage.py中拷贝下面语句并复制到test.py中,或另外自己新建的测试文件也可以.

import os
import sys

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "library00.settings")

然后导入模块,方法和文件

import django
django.setup()

from app01 import models  #待测试文件的导入一定要在django.setup之后

配置显示sql语句日志

settings中加下列代码

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

数据的操作

建表

from django.db import models

# Create your models here.

class Book(models.Model):
    name = models.CharField(max_length=64)
    price = models.DecimalField(max_digits=8,decimal_places=2)
    publicdata = models.DateField(auto_now_add=True)
    public = models.ForeignKey(to="Public")
    author = models.ManyToManyField(to="Author")


class Public(models.Model):
    name = models.CharField(max_length=64)
    addr = models.CharField(max_length=64)


class Author(models.Model):
    name = models.CharField(max_length=64)
    enail = models.EmailField()
    author_detail = models.OneToOneField(to="AuthorDetail")

class AuthorDetail(models.Model):
    phone = models.BigIntegerField()
    addr = models.CharField(max_length=64)

表数据操作

增

方法一

#手动添加数据
# book_obj = models.Books.objects.create(title='harry',price=120.12,publishdata='2019-11-11')
#自动导入时间
import datetime
ctime = datetime.datetime.today()
book_obj = models.Books.objects.create(title='potter',price=24.034,publishdata=ctime)

方法二

#生成对象,后使用save()方法
book_obj = models.Books(title="西游记",price=333.445,publishdata="1994-2-3")
book_obj.save()

改

方法一

#使用queryset方法
models.Books.objects.filter(pk=1).update(price=333)

方法二

#利用对象
book_obj = models.Books.objects.get(pk=1)
book_obj.price=222
book_obj.save()

不推荐使用这种方法,这种方式实际上是将每部数据所有字段全部重写一边,效率低

删

方法一

#利用queryset方法 delete()
models.Books.objects.filter(pk=3).delete()

方法二

#对象方法
book_obj = models.Books.objects.get(pk=3)
book_obj.delete()

对象方法直接.delete().不需要保存.

查(13条)

django的ORM语句默认惰性查询,当你需要使用数据的时候才执行ORM语句

1.all()

查询所有,返回QuerySet对象,形式列表套对象

book_obj = models.Books.objects.all()
print(book_obj)
print(type(book_obj))
>>>
(0.000) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` LIMIT 21; args=()
<QuerySet [<Books: Books object>, <Books: Books object>]>
<class 'django.db.models.query.QuerySet'>

2.filter()

筛选,返回queryset对象,列表套对象

相当于原生sql中的where关键字

支持多个参数同时查询,相当于and关系,即返回同时成立对象

book_obj = models.Books.objects.filter(id=2,title="potter")
print(book_obj)
print(type(book_obj))
>>>
(0.003) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE (`app01_books`.`id` = 2 AND `app01_books`.`title` = 'potter') LIMIT 21; args=(2, 'potter')
<QuerySet [<Books: Books object>]>
<class 'django.db.models.query.QuerySet'>

3.get()

筛选,返回一个对象

book_obj = models.Books.objects.get(pk=2)
print(book_obj)
print(type(book_obj))
>>>
(0.003) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE `app01_books`.`id` = 2; args=(2,)
Books object
<class 'app01.models.Books'>

get和filter的区别

1. filter获取到的是一个quertset对象,类似于一个列表,条件不存在时,返回一个空
2. get获取到的直接是数据对象本身,条件不存在时,报错

4.first()

返回queryset中第一个数据对象

book_obj = models.Books.objects.filter(pk=2)
book = book_obj.first()
print(book_obj,"1")
print(type(book_obj))
print(book,"2")
print(type(book))
>>>
(0.001) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE `app01_books`.`id` = 2 ORDER BY `app01_books`.`id` ASC LIMIT 1; args=(2,)
(0.000) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE `app01_books`.`id` = 2 LIMIT 21; args=(2,)
<QuerySet [<Books: Books object>]> 1
<class 'django.db.models.query.QuerySet'>
Books object 2
<class 'app01.models.Books'>

5.last()

返回queryset中第最后一个数据对象

book_obj = models.Books.objects.filter(pk=2)
book = book_obj.last()
print(book_obj,"1")
print(type(book_obj))
print(book,"2")
print(type(book))
>>>
(0.001) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE `app01_books`.`id` = 2 ORDER BY `app01_books`.`id` DESC LIMIT 1; args=(2,)
(0.000) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE `app01_books`.`id` = 2 LIMIT 21; args=(2,)
<QuerySet [<Books: Books object>]> 1
<class 'django.db.models.query.QuerySet'>
Books object 2
<class 'app01.models.Books'>

6.count()

统计数据的个数 返回整型

book_obj = models.Books.objects.count()
print(book_obj)
print(type(book_obj))
>>>
(0.003) SELECT COUNT(*) AS `__count` FROM `app01_books`; args=()
2
<class 'int'>

注意:count()前时objects,默认计算表内所有数据,前面亦可加其他筛选条件

7.values()

获取数据对象中指定的字段的值,返回queryset对象,形式为列表套字典.

book_obj = models.Books.objects.values("title","price")
book = book_obj.first()
print(book_obj)
print(type(book_obj))
print(book)
print(type(book))
>>>
(0.000) SELECT `app01_books`.`title`, `app01_books`.`price` FROM `app01_books` ORDER BY `app01_books`.`id` ASC LIMIT 1; args=()
(0.001) SELECT `app01_books`.`title`, `app01_books`.`price` FROM `app01_books` LIMIT 21; args=()
<QuerySet [{'title': 'potter', 'price': Decimal('24.03')}, {'title': '西游记', 'price': Decimal('333.44')}]>
<class 'django.db.models.query.QuerySet'>
{'title': 'potter', 'price': Decimal('24.03')}
<class 'dict'>

8.values_list()

获取数据对象中指定的字段的值,返回queryset对象,形式为列表套元组.

book_obj = models.Books.objects.values_list("title","price")
book = book_obj.first()
print(book_obj)
print(type(book_obj))
print(book)
print(type(book))
print(book[0])
print(type(book[0]))
print(book[1])
print(type(book[1]))
>>>
(0.000) SELECT `app01_books`.`title`, `app01_books`.`price` FROM `app01_books` ORDER BY `app01_books`.`id` ASC LIMIT 1; args=()
(0.001) SELECT `app01_books`.`title`, `app01_books`.`price` FROM `app01_books` LIMIT 21; args=()
<QuerySet [('potter', Decimal('24.03')), ('西游记', Decimal('333.44'))]>
<class 'django.db.models.query.QuerySet'>
('potter', Decimal('24.03'))
<class 'tuple'>
potter
<class 'str'>
24.03
<class 'decimal.Decimal'>

9.order_by()

按照指定字段排序,默认升序,返回queryset对象,形式列表套对象

book_obj = models.Books.objects.order_by("id")
print(book_obj)
print(type(book_obj))
book = book_obj.first()
print(book)
print(type(book))
>>>
(0.013) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` ORDER BY `app01_books`.`id` ASC LIMIT 21; args=()
(0.001) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` ORDER BY `app01_books`.`id` ASC LIMIT 1; args=()
<QuerySet [<Books: Books object>, <Books: Books object>]>
<class 'django.db.models.query.QuerySet'>
Books object
<class 'app01.models.Books'>

注意:如果要降序,在字段前加负号走-

10.reverse()

对查询结果颠倒顺序,返回queryset对象,形式列表对象

也就是前提必须时排序的输出才可以使用reverse()方法

book_obj = models.Books.objects.order_by("id")
res = book_obj.reverse()
print(book_obj)
print(res)
>>>
(0.001) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` ORDER BY `app01_books`.`id` ASC LIMIT 21; args=()
(0.000) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` ORDER BY `app01_books`.`id` DESC LIMIT 21; args=()
<QuerySet [<Books: Books object>, <Books: Books object>]>
<QuerySet [<Books: Books object>, <Books: Books object>]>

11.exclude()

排除()中选项,返回queryset对象,形式列表套对象

book_obj = models.Books.objects.exclude(id=2)
print(book_obj)
print(type(book_obj))
>>>
(0.001) SELECT `app01_books`.`id`, `app01_books`.`title`, `app01_books`.`price`, `app01_books`.`publishdata` FROM `app01_books` WHERE NOT (`app01_books`.`id` = 2) LIMIT 21; args=(2,)
<QuerySet [<Books: Books object>, <Books: Books object>]>
<class 'django.db.models.query.QuerySet'>

12.exists()

判断查询结果是否有值,返回bool

book_obj = models.Books.objects.exclude(id=2).exists()
print(book_obj)
print(type(book_obj))
>>>
(0.000) SELECT (1) AS `a` FROM `app01_books` WHERE NOT (`app01_books`.`id` = 2) LIMIT 1; args=(2,)
True
<class 'bool'>

13.distinct()

对查询结果去重,返回queryset对象,形式列表套字典

去重的前提是数据必须完全一致,id容易忽略

book_obj = models.Books.objects.values("title","price").distinct()
print(book_obj)
print(type(book_obj))
>>>
(0.000) SELECT DISTINCT `app01_books`.`title`, `app01_books`.`price` FROM `app01_books` LIMIT 21; args=()
<QuerySet [{'title': 'potter', 'price': Decimal('24.03')}, {'title': '西游记', 'price': Decimal('333.44')}, {'title': 'herry', 'price': Decimal('555.00')}]>
<class 'django.db.models.query.QuerySet'>

注意事项

1. 小数字段,超出范围,自动四舍五入

2. django建表字段默认not null

3. 时间字段输入格式必须为:It must be in YYYY-MM-DD format.

4. pk会自动找到当前表的主键字段

5. filter查询出来的是一个queryset对象

   queryset对象可以无线调用queryset的方法(链式无线点下去)

   queryset对象可以使用res.query方法查看当前结果内部对应的sql语句.

双下划线查询

# 查询价格大于500的书籍
res = models.Books.objects.filter(price__gt=500)
print(res)
# 查询价格小于400 的书籍
res = models.Books.objects.filter(price__lt=400)
print(res)
# 查询价格大于等于500
res = models.Books.objects.filter(price__gte=444.66)  对数字精确度不敏感
res = models.Books.objects.filter(price__gte=500)
print(res)
# 查询价格小于等于500的书籍
res = models.Books.objects.filter(price__lte=500)
print(res)

# 查询价格是222.66或者444.22或者500的书籍
res = models.Books.objects.filter(price__in=[222,444,500])
print(res)

# 查询价格在200到800之间的书籍
res = models.Books.objects.filter(price__range=(200,800))  # 顾头顾尾
print(res)

# 查询出版日期是2019年的书籍
res = models.Books.objects.filter(publishdate__year='2019')
print(res)
# 查询出版日期是1月份的书籍
res = models.Books.objects.filter(publishdate__month='1')
print(res)

模糊查询

MySQL中的模糊查询

关键字like

模糊匹配符号

​ %:匹配任何个数的任意字符

​ _:匹配一位任意的字符

# 查询书籍是以三开头的书
res = models.Books.objects.filter(title__startswith='三')
print(res)
# 查询书籍是以义结尾的书
res = models.Books.objects.filter(title__endswith='1')
print(res)
# 查询书籍名称中包含游字的书籍
res = models.Books.objects.filter(title__contains='游')
print(res)

# 查询书籍名称中包含字母p的书籍
res = models.Books.objects.filter(title__contains='p')  # 默认区分大小写
res = models.Books.objects.filter(title__icontains='p')  # 忽略大小写 加i
print(res)

多表操作

一对多表

增

方法一:

直接传表里面的实际字段 跟数据主键值

models.Book.objects.create(title='三国演义',price=222.33,publish_id=1)

方法二:

传虚拟字段 跟数据对象即可

publish_obj = models.Publish.objects.filter(pk=2).first()
models.Book.objects.create(title='红楼梦',price=444.33,publish=publish_obj)#publish为外键

查

同上

改

方法一,直接update

models.Book.objects.filter(pk=1).update(publish_id=2)

方法二,通过对象

publish_obj = models.Publish.objects.filter(pk=1).first()
models.Book.objects.filter(pk=1).update(publish=publish_obj)

删

默认就是级联删除 级联更新

models.Publish.objects.filter(pk=1).delete() 

多对多表

增

add方法,先选出对象,使用对象.方法到达表

#先获取对象
book_obj = models.Book.objects.filter(pk=2).first()
#使用.方法到达被关联表
book_obj.author.all()

向第三张关系表添加数据

支持传数字 add(1,2)

book_obj.author.add(1,2)
>>>
(0.191) INSERT INTO `app01_book_author` (`book_id`, `author_id`) VALUES (2, 1), (2, 2); args=(2, 1, 2, 2)

也支持传对象 add(author_obj,author_obj)

#获取author对象
author_obj = models.Author.objects.filter(pk=1).first()
book_obj.authors.add(author_obj,author_obj1)

add后可传至少一个值

改

set()方法修改多对多表关系中的数据

可以传数字或是对象

book_obj = models.Book.objects.filter(pk=2).first()
book_obj.authors.set((1,3))
book_obj.authors.set([1,])
author_obj = models.Author.objects.filter(pk=1).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
book_obj.authors.set((author_obj,author_obj1))

()中必须是可迭代对象,可迭代对可为多个

删

remove方法

remove可以传数字或者对象,支持多个

book_obj = models.Book.objects.filter(pk=2).first()
book_obj.authors.remove(100)
book_obj.authors.remove(1,2)
author_obj = models.Author.objects.filter(pk=1).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
book_obj.authors.remove(author_obj)
book_obj.authors.remove(author_obj,author_obj1)

clear()方法

清空筛选出来所有记录,不需要传参

book_obj = models.Book.objects.filter(pk=2).first()
book_obj.authors.clear()

跨表查询

正反向查询

外键字段在谁那,由谁查,谁就是正向

正向查询按字段

反向查询按表名小写

子查询

基于对象的跨表查询

相当于子查询,分布操作

# 1.查询书籍主键为2的出版社名称
book_obj = models.Book.objects.filter(pk=2).first()
print(book_obj.publish)  # 出版社对象
print(book_obj.publish.name)
# 2.查询书籍主键为4的作者姓名?
book_obj = models.Book.objects.filter(pk=4).first()
print(book_obj.authors)  # app01.Author.None
print(book_obj.authors.all())
# 3.查询作者是jason的手机号码
author_obj = models.Author.objects.filter(name='jason').first()
print(author_obj.author_detail)
print(author_obj.author_detail.phone)
# 4.查询出版社是东方出版社出版过的书籍
publish_obj = models.Publish.objects.filter(name='东方出版社').first()
# print(publish_obj.book_set)  # app01.Book.None
print(publish_obj.book_set.all())

# 5.查询作者是jason写过的书籍
author_obj = models.Author.objects.filter(name='jason').first()
# print(author_obj.book_set)  # app01.Book.None
print(author_obj.book_set.all())  # app01.Book.None

# 6.查询手机号是120的作者姓名
author_detail_obj = models.AuthorDetail.objects.filter(phone=120).first()
print(author_detail_obj.author)
print(author_detail_obj.author.email)

注意

出现app01.Book.None,加all()

当正向查询点击外键字段数据有多个的情况下 需要.all()

当反向查询的时候表名小写需要加_set

all()和_set在一对多和多对多的时候加,一对一的时候不加

基于双下划线的跨表操作

相当于mysql的联表操作

MySQL中的union:两边都写,不足的补null

models后面点的谁 就以谁为基表

# 1.查询书籍pk为2的出版社名称
# 正向
res = models.Book.objects.filter(pk=2).values('publish__name')  # 写外键字段就相当于已经跨到外键字段所关联的表
# 你想要改表的哪个字段信息 你只需要加__获取即可
print(res)
# 反向
res = models.Publish.objects.filter(book__pk=2).values('name')
print(res)

# 2.查询书籍pk为2的作者姓名和邮箱
res = models.Book.objects.filter(pk=2).values('authors__name','authors__email')
print(res)
res = models.Author.objects.filter(book__pk=2).values('name','email')
print(res)

# 3.查询作者是egon的家庭地址
res = models.Author.objects.filter(name='egon').values('author_detail__addr')
print(res)
res = models.AuthorDetail.objects.filter(author__name='egon').values('addr')
print(res)

# 4.查询出版社是东方出版社出版过的书的名字
res = models.Publish.objects.filter(name='东方出版社').values('book__title')
print(res)
res = models.Book.objects.filter(publish__name='东方出版社').values('title')
print(res)

# 5.查询书籍pk是2的作者的手机号  三表联查  可无限跨表联查values(外键字段1__外键字段2__外键字段3,,,)
res = models.Book.objects.filter(pk=2).values('authors__author_detail__phone')
print(res)
res = models.Author.objects.filter(book__pk=2).values('author_detail__phone')
print(res)

choice参数

class User(models.Model):
    username = models.CharField(max_length=64)
    password = models.IntegerField()
    gender_choices = (
        (1,'男'),
        (2,'女'),
        (3,'其他'),
    )
    gender = models.IntegerField(choices=gender_choices)

数据库存储参数,调字段返回参数都是原数字,无论在不在gender_choices里面

若想显示男,女或其他,使用

user_obj.get_gender_display()

即可,若数据库中存了4,使用display()方法返回4.

多对多三种创建方法

django的orm中为了方便管理,使用创建第三张表的方法管理多对多表

1. 全自动

   class Book(models.Model):
       title = models.CharField(max_length=32)
       # 多对多关系字段
       authors = models.ManyToManyField(to='Authors')
   
   class Authors(models.Model):
       name = models.CharField(max_length=32)
   

   优点:orm全自动建表,还提供了add,remove,set,clear四个内置方法

   缺点:自动创建的第三张表无法扩展修改字段,无扩展性

2. 纯手写

   class Book(models.Model):
   	title = models.CharField(max_length=32)
   	
   class Authors(models.Model):
   	name = models.CharField(max_length=32)
   
   class Book2Authors(models.Model):
   	book = models.ForeignKey(to="Book")
   	author = models.ForeignKey(to="Authors")
   	create_time = models.DateField(auto_now_add = True)
   
   

   优点:纯手动操作,完全可控

   缺点:不支持跨表查询,不存在正反向查询的概念

3. 半自动(推荐使用)

   class Book(models.Model):
       title = models.CharField(max_length=32)
           # 多对多关系字段
       authors =models.ManyToManyField(to="Authors",through="Book2Author",through_fields=("book","authors"))
   
   class Authors(models.Model):
       name = models.CharField(max_length=32)
       #或者写在这里面,同理
       #book = models.ManyToManyField(to="Book",through="Book2Author",through_fields=("authors","book"))
       
   class Book2Author(models.Model):
       book = models.ForeignKey(to="Book")
       authors = models.ForeignKey(to="Authors")
   

   说明:

   当你的ManyToManyField只有一个参数to的情况下 orm会自动帮你创建第三张表

   如果你加了through和through_fields那么orm就不会自动帮你创建第三张表 但是它会在内部帮你维护关系让你能够继续使用orm的跨表查询

   through 自己指定第三张关系表

   through_fields 自己指定第三张关系表中 在谁的类中,谁就放前面

   优点:

   可以任意添加和修改第三张表中的字段

   表中可以有任意多的外键字段

   可以任意扩展字段

   支持orm跨表查询

   缺点:

   不支持add.remove,clear,set方法