Django之ORM操作

 此篇依赖于上一篇：Django之模型注册

我们在models.py中定义的表结构，在admin.py中将它们注册进去了，现在我们登陆django admin界面就会看到我们注册的所有表。

 数据如下：

 

现在我们来拿Blog表，进行增删改查操作，它总共两个字段name,tagline。

进入项目目录下，执行:

 增删改

增加记录

python manager.py shell
from orm.models import Blog
b = Blog(name="javaScript",tagline="java model")
b.save()
或
Blog(name="Windows",tagline="Windows model").save()
或
Blog.objects.create(name="Onesen",tagline="Onesen Ui")

 

删除记录

Blog.objects.filter(name="Linux").delete()

 

修改记录

 

Blog.objects.filter(name="Onesen").update(tagline="TwoSen")
Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')
# 将原有数据批量自增
Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)

 

关联：

以Blog表和Entry表的关联为例

ForeignKey的关联修改

# Blog表是Entry表的外键
from orm.models import Entry
# 找出一条Blog记录
cheese_blog = Blog.objects.get(name="Windows")
# 找出一条Entry记录
entry = Entry.objects.get(pk=1)
# 将Entry表的blog与查询到的blog关联
entry.blog = cheese_blog
# 保存Entry设置
entry.save()

 

ManyToManyField关联修改

 Entry表中authors = models.ManyToManyField(Author)

以Author表和Entry表的关联人例，现在给Entry表的authors字段关联多个Author

关联前

开始关联

# 从Author表中选择出3个对象
from orm.models import Author
author1 = Author.objects.get(pk=1)
author2 = Author.objects.get(pk=2)
author3 = Author.objects.get(pk=3)

# 从Entry表中选择出一个对象
from orm.models import Entry
entry = Entry.objects.get(pk=1)
# 将从Author表选择出的3个对象关联到Entry表选择出的1个对象中
entry.authors.add(author1,author2,author3)

关联后

 

 

普通查询

 除了可以通过python manager.py shell来查询数据以外，还可以通过pycharm来查询数据。

 

例如：

#!/usr/bin/env python
# _*_ coding:utf-8 _*_

import os
os.environ['DJANGO_SETTINGS_MODULE'] = 'Blog.settings'
import django
django.setup()

from orm import models
auth = models.Author.objects.get(pk=1)
print auth

 

单表查询

#!/usr/bin/env python
# _*_ coding:utf-8 _*_

import os
os.environ['DJANGO_SETTINGS_MODULE'] = 'Blog.settings'
import django
django.setup()


import datetime
from orm import models

# 单条查询用get,不可返回多条数据
models.Entry.objects.get(pk=1)
models.Entry.objects.get(headline__exact='technology')
models.Entry.objects.get(headline__iexact='technology')  # 大小写不敏感
models.Entry.objects.get(headline__contains='nology')
models.Entry.objects.get(headline__icontains='nology')
# 多条查询用filter
models.Entry.objects.filter(pub_date__year=2017)
models.Entry.objects.all().filter(pub_date__year=2017)
models.Entry.objects.filter(pub_date__gt='2017-03-30')
# 排除用exclude
datas = models.Entry.objects.filter(
    headline__startswith=u"文学"
).exclude(
    pub_date__gt=datetime.date.today()
)
# 查询出固定的条数用切片
models.Entry.objects.all()[:2]
# 排序操作用order_by
models.Entry.objects.order_by('pub_date')[0]
# 查询所有用all
models.Entry.objects.all()

 

关联查询

 不同表之间关联查询

#!/usr/bin/env python
# _*_ coding:utf-8 _*_

import os
os.environ['DJANGO_SETTINGS_MODULE'] = 'Blog.settings'
import django
django.setup()

import datetime
from orm import models

# 与entry.headline模糊匹配tech的所有记录有关联的Blog表的所有记录
models.Blog.objects.filter(entry__headline__contains='tech')
# 与Blog.name精确匹配Windows的所有记录有关联的Entry表的所有记录
models.Entry.objects.filter(blog__name__exact='Windows')

 

同一张表的不同字段的操作

#!/usr/bin/env python
# _*_ coding:utf-8 _*_

import os
os.environ['DJANGO_SETTINGS_MODULE'] = 'Blog.settings'
import django
django.setup()

from orm import models

from django.db.models import F
# 查询Entry表的所有记录，条件是n_pingbacks值大于n_comments
models.Entry.objects.filter(n_pingbacks__gt=F('n_comments'))
# 查询Entry表的所有记录，条件是n_pingbacks值大于rating*1.5
models.Entry.objects.filter(n_pingbacks__gt=F('rating')*1.5)

from datetime import timedelta
# 查询Entry表的所有记录，条件是mod_date大于pub_date加3天
models.Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))

 

Caching and QuerySets

每个QuerySet包含一个Cache来最小化数据库访问。明白它是如何工作的将有利于你写出高效的代码。
当产生一个新的QuerySet时，它的Cache是空的。当第一次操作QuerySet时，发生了数据库查询，Django在返回精确请求结果的同时将查询结果保存在QuerySet的Cache中，随后对QuerySet的访问将重用Cache中的结果

# 一次数据库查询，随后都是Cache访问
queryset = Entry.objects.all()  # 产生空的QuerySet
[entry for entry in queryset] # Queries the database,对QuerySet做了完全评估，此时QuerySet会Cache结果集
print queryset[5] # Uses cache
print queryset[5] # Uses cache

# 此时会产生一次数据库查询，一次利用Cache
queryset = Entry.objects.all()
print([p.headline for p in queryset]) # Queries the database,对QuerySet做了完全评估，此时QuerySet会Cache结果集
print([p.headline for p in queryset])

# 两次数据库查询
queryset = Entry.objects.all()
print queryset[5]  # Queries the database，对QuerySet做了部分评估，此时QuerySet不Cache结果集
print queryset[5]  # Queries the database again

# 此时将会产生两次数据库查询
print([e.headline for e in Entry.objects.all()])
print([e.headline for e in Entry.objects.all()])

 

复杂查询

Q对象可用来封装查询关键字，需要从django.db.models中导入Q方法

# Q实现一个简单的LIKE查询
from django.db.models import Q
Q(question__startswith='What')

# 使用&或|来将两个Q对象组合成一个新的Q对象
Q(question__startswith='Who') | Q(question__startswith='What')
这个操作类似以下WHERE语句
WHERE question LIKE 'Who%' OR question LIKE 'What%'
# Q对象使用~来执行一个否操作
Q(question__startswith='Who') | ~Q(pub_date__year=2005)

# 如果同时罗列出多个Q查询，则它们会被AND到一起
Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
换成SQL语句则为：
SELECT * from polls WHERE question LIKE 'Who%'
    AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
		
# 当Q对象和关键字参数结合使用的时候，Q对象必须放在关键字前面
如，以下语句是合法的
Poll.objects.get(
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
    question__startswith='Who')
否则不合法
Poll.objects.get(
    question__startswith='Who',
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))	

 

聚合查询

#!/usr/bin/env python
# _*_ coding:utf-8 _*_

import os
os.environ['DJANGO_SETTINGS_MODULE'] = 'Blog.settings'
import django
django.setup()

from orm import models

# 总计
models.Entry.objects.count()
models.Entry.objects.filter(headline__startswith='tech').count()

# 平均值
from django.db.models import Avg
models.Entry.objects.aggregate(Avg('rating'))
models.Entry.objects.all().aggregate(Avg('rating'))

# 最大值
from django.db.models import Max
models.Entry.objects.aggregate(Max('rating'))
models.Entry.objects.all().aggregate(Max('rating'))

 

更多聚合查询的例子：https://docs.djangoproject.com/en/1.9/topics/db/aggregation/

 

用户认证

from django.contrib.auth import authenticate
user = authenticate(username='john', password='secret')
if user is not None:
    # the password verified for the user
    if user.is_active:
        print("User is valid, active and authenticated")
    else:
        print("The password is valid, but the account has been disabled!")
else:
    # the authentication system was unable to verify the username and password
    print("The username and password were incorrect.")

 

用户退出

from django.contrib.auth import logout
 
def logout_view(request):
    logout(request)
    # Redirect to a success page.