Django_QuerySet

QuerySet

  • Retrieving all objects: 查詢所有數(shù)據(jù)記錄，SELECT *

    all_entries = Entry.objects.all() : returns a QuerySet of all the objects in the database.

  • Retrieving specific objects with filters，根據(jù)限定條件查詢，SELECT ... WHERE...

    • filter(**kwargs): Returns a new QuerySet containing objects that match the given lookup parameters.

    • exclude(**kwargs): Returns a new QuerySet containing objects that do not match the given lookup parameters.

      Entry.objects.filter(pub_date__year=2006) == Entry.objects.all().filter(pub_date__year=2006)

  • Retrieving a single object with get()

    one_entry = Entry.objects.get(pk=1)

  • Limiting QuerySet: This is the equivalent of SQL's LIMIT and OFFSET clauses.

    Entry.objects.all()[:5] : first 5 objects (LIMIT 5):
    Entry.objects.all()[5:10] : the sixth through tenth objects (OFFSET 5 LIMIT 5):

  • Negative indexing id not support neither does slice with step like Entry.objects.all()[:10:2]

Field lookups

Entry.objects.filter(blog_id=4)
Entry.objects.get(headline__exact="Cat bites dog") == SELECT ... WHERE headline = 'Cat bites dog';
Blog.objects.get(id__exact=14) # Explicit form
Blog.objects.get(id=14) # __exact is implied
Blog.objects.get(name__iexact="beatles blog") => "Beatles Blog", "beatles blog", or even "BeAtlES blOG".
Entry.objects.get(headline__contains='Lennon')
startswith, endswith, istartswith, iendswith

Lookups that span relationships: 深入關(guān)系內(nèi)查詢

Entry.objects.filter(blog__name='Beatles Blog')
Blog.objects.filter(entry__headline__contains='Lennon')
Blog.objects.filter(entry__authors__name='Lennon')
Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)
Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008) => 2條件的交集
Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008) => 2條件的并集

Suppose there is only one blog that had both entries containing "Lennon" and entries from 2008, but that none of the entries from 2008 contained "Lennon". The first query would not return any blogs, but the second query would return that one blog.
如果有一個(gè)blog，包含有條目，其中有的是包括 “Lennon”字段的，有的是2008之后發(fā)布的，但是沒有哪個(gè)條目是同時(shí)包含2個(gè)條件的，所以針對(duì)第一個(gè)QuerySet，同時(shí)滿足條件的條目并不存在，返回空的Set，但是第二Query，滿足有包含Lennon和2008之后發(fā)表的blog是可以查詢到的，所以返回該blog。

Blog.objects.exclude(
    entry__headline__contains='Lennon',
    entry__pub_date__year=2008,
)

以上表示 在Blog中去除headline包含Lennon或者2008發(fā)布的集合

Blog.objects.exclude(
    entry__in=Entry.objects.filter(
        headline__contains='Lennon',
        pub_date__year=2008,
    )
)

以上表示去除headline包括Lennon并且2008發(fā)布的條目以外的數(shù)據(jù)

  • Filters can reference fields on the model, 同一個(gè)model內(nèi)的不同數(shù)據(jù)作比較，可以理解為在一句query內(nèi)查詢多個(gè)數(shù)據(jù)進(jìn)行比較
>>> from django.db.models import F
>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks'))

>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)
>>> Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks'))
>>> Entry.objects.filter(authors__name=F('blog__name'))

>>> from datetime import timedelta
>>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))
  • The pk lookup shortcut
>>> Blog.objects.get(id__exact=14) # Explicit form
>>> Blog.objects.get(id=14) # __exact is implied
>>> Blog.objects.get(pk=14) # pk implies id__exact

# Get blogs entries with id 1, 4 and 7
>>> Blog.objects.filter(pk__in=[1,4,7])
# Get all blog entries with id > 14
>>> Blog.objects.filter(pk__gt=14)

>>> Entry.objects.filter(blog__id__exact=3) # Explicit form
>>> Entry.objects.filter(blog__id=3)        # __exact is implied
>>> Entry.objects.filter(blog__pk=3)        # __pk implies __id__exact

>>> Entry.objects.filter(headline__contains='%')
>>> SELECT ... WHERE headline LIKE '%\%%';
  • Caching and QuerySets
>>> print([e.headline for e in Entry.objects.all()])
>>> print([e.pub_date for e in Entry.objects.all()])
>>> 以上會(huì)產(chǎn)生2次數(shù)據(jù)庫請(qǐng)求

>>> queryset = Entry.objects.all()
>>> print([p.headline for p in queryset]) # Evaluate the query set.
>>> print([p.pub_date for p in queryset]) # Re-use the cache from the evaluation
>>> 如果已經(jīng)遍歷過一次QS，下一次則會(huì)使用cache
  • When QuerySets are not cached
>>> queryset = Entry.objects.all()
>>> print(queryset[5]) # Queries the database
>>> print(queryset[5]) # Queries the database again

>>> queryset = Entry.objects.all()
>>> [entry for entry in queryset] # Queries the database
>>> print(queryset[5]) # Uses cache
>>> print(queryset[5]) # Uses cache
  • Complex lookups with Q objects
Q(question__startswith='Who') | Q(question__startswith='What') == WHERE question LIKE 'Who%' OR question LIKE 'What%'


Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
) ==>
SELECT * from polls WHERE question LIKE 'Who%' AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

如果有Q操作和關(guān)鍵字的操作，Q操作至前
1. ok
Poll.objects.get(
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
    question__startswith='Who',
)
2. not valid
Poll.objects.get(
    question__startswith='Who',
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
  • Deleting objects
>>> Entry.objects.filter(pub_date__year=2005).delete()
(5, {'webapp.Entry': 5})
  • Deleting objects
>>> e.delete()
(1, {'weblog.Entry': 1})

Entry.objects.filter(pub_date__year=2005).delete()
(5, {'webapp.Entry': 5})

默認(rèn)會(huì)刪除外鍵所關(guān)聯(lián)的其他表的內(nèi)容，ON DELETE CASCAD
b = Blog.objects.get(pk=1)
# This will delete the Blog and all of its Entry objects.
b.delete()

delete只作用到queryset，所以刪除全部就是以下方法
Entry.objects.all().delete()
  • Copying model instances
blog = Blog(name='My blog', tagline='Blogging is easy')
blog.save() # blog.pk == 1
設(shè)置pk為none，再次保存就會(huì)生成一個(gè)新的記錄
blog.pk = None
blog.save() # blog.pk == 2
  • Updating multiple objects at once
  • 對(duì)象的對(duì)應(yīng)關(guān)系
    • One-To-Many 關(guān)系
    • 正向獲取 父表 to 子表
e = Entry.objects.get(id=2)
e.blog # 返回通過外鍵關(guān)聯(lián)的 blog 對(duì)象
#如果要更新 e 對(duì)象的 blog 屬性
b = Blog.objects.get(id=3)
e.blog = b
e.save() # 執(zhí)行根系操作，
 
one-to-many 關(guān)系在第一次使用后將會(huì)被緩存
e = Entry.objects.get(id=2)
print(e.blog) # 查詢數(shù)據(jù)， 并將數(shù)據(jù)緩存
print(e.blog) # 不查詢數(shù)據(jù)庫， 之間中緩存中讀取

使用 QuerySet 的 select_related() 方法時(shí)， 會(huì)將相應(yīng)的 one-to-many 關(guān)系的對(duì)象都預(yù)先取出來并緩存, 在真正使用時(shí)就不會(huì)訪問數(shù)據(jù)庫
e = Entry.objects.select_related().get(id=2)
print(e.blog) # 不查詢數(shù)據(jù)庫
print(e.bong) # 不查詢數(shù)據(jù)庫
  • 反向獲取 子表 to 父表
如果 model A 通過 ForeignKey字段 field 與 model B 想關(guān)聯(lián)。 
B 對(duì)象可以通過 model Manager 去訪問與之對(duì)應(yīng)的所有的 A 對(duì)象。 
默認(rèn)的， 這個(gè) model Manage 名為 foo_set, 其中 foo 是擁有外鍵那個(gè) model 名的小寫， 即 a_set()
例： 通過 Blog 對(duì)象查詢 Entry 對(duì)象：
    
# 查詢與 Blog 對(duì)象 b 關(guān)聯(lián)的所有 entry 對(duì)象
b = Blog.objects.get(pk=2)
b.entry_set.all()
 
# 查詢與 Blog 對(duì)象 b 關(guān)聯(lián)的 entry 對(duì)象中 headline 包含 'Lennon' 的
b.entry_set.filter(headline__contains='Lennon')

如果在定義 ForeignKey 字段時(shí) 通過 related_name 可以更改這個(gè)默認(rèn)的 foo_set() Manage 方法。
例如： 將最頂部的 Entry Model 中的 blog 字段修改成如下：
blog = ForeignKey(Blog, related_name=’entries’), 上面的代碼中的 entry_set 就可以都改成 entries
    
# 查詢與 Blog 對(duì)象 b 關(guān)聯(lián)的所有 entry 對(duì)象
b = Blog.objects.get(pk=2)
b.entries.all()
 
# 查詢與 Blog 對(duì)象 b 關(guān)聯(lián)的 entry 對(duì)象中 headline 包含 'Lennon' 的
b.entries.filter(headline__contains='Lennon')
  • One-To-One 關(guān)系
One-to-one 關(guān)系同 many-to-one 非常相似， API 用法與 many-to-one 的基本也基本一致

class EntryDetail(models.Model):
    entry = models.OneToOneField(Entry, on_delete=models.CASCADE)
    details = models.TextField()

ed = EntryDetail.objects.get(pk=3)
en.entry # 返回與之對(duì)應(yīng)的 Entry 對(duì)象

與 many-to-one 不同的是其反向查找， 如下：    
e = Entry.objects.get(pk=3)
# 取得與 Entry 對(duì)象對(duì)應(yīng)的 EntryDetail 對(duì)象，
# 只需調(diào)用 EntryDetail 的小寫 entrydetail 即可
e.entrydetail
 
#更新
e.entrydetail = ed2
e.save()
  • 通過關(guān)聯(lián)對(duì)象查詢
當(dāng)查詢過濾條件傳入 filter 函數(shù)是，既可以使用整個(gè)對(duì)象，可以使用相應(yīng)的對(duì)象值。
# Blog 對(duì)象 b 的 id = 5
Entry.objects.filter(blog=b) # 通過整個(gè)對(duì)象進(jìn)行查詢
Entry.objects.filter(blog=b.id) # 通過 b 的 id 屬性查詢
Entry.objects.filter(blog=5) # 直接用 id 值查詢 hard code
  • end
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容