__exact? ? ? ? 精確等于 like 'aaa'__iexact? ? 精確等于 忽略大小寫 ilike'aaa'__contains? ? 包含 like '%aaa%'__icontains? ? 包含 忽略大小寫 ilike '%aaa%'呀闻，但是對于sqlite來說拗盒，contains的作用效果等同于icontains怠噪。

django database gammer

__gt? ? 大于

__gte? ? 大于等于

__lt? ? 小于

__lte? ? 小于等于

__in? ? 存在于一個(gè)list范圍內(nèi)

__startswith? 以...開頭

__istartswith? 以...開頭 忽略大小寫

__endswith? ? 以...結(jié)尾

__iendswith? ? 以...結(jié)尾烟号，忽略大小寫

__range? ? 在...范圍內(nèi)

__year? ? ? 日期字段的年份

__month? ? 日期字段的月份

__day? ? ? ? 日期字段的日

__isnull=True/False__isnull=True與 __exact=None的區(qū)別classBlog(models.Model):name = models.CharField(max_length=100)

tagline = models.TextField()def__unicode__(self):returnself.nameclassAuthor(models.Model):name = models.CharField(max_length=50)

email = models.EmailField()def__unicode__(self):returnself.nameclassEntry(models.Model):blog = models.ForeignKey(Blog)

headline = models.CharField(max_length=255)

body_text = models.TextField()

pub_date = models.DateTimeField()

authors = models.ManyToManyField(Author)def__unicode__(self):returnself.headline

這 是model鸵荠，有blog，author啤誊，以及entry酌媒；其中entry分別與blog與author表關(guān) 聯(lián)，entry與blog表是通過 外鍵(models.ForeignKey())相連耸弄，屬于一對多的關(guān)系注盈，即一個(gè)entry對應(yīng)多個(gè)blog，entry與author是多對多的關(guān)系叙赚， 通過modles.ManyToManyField()實(shí)現(xiàn)。

一僚饭、插入數(shù)據(jù)庫震叮，用save()方法實(shí)現(xiàn)，如下：>>>frommysite.blog.modelsimportBlog>>>b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')>>>b.save()

二鳍鸵、更新數(shù)據(jù)庫苇瓣，也用save()方法實(shí)現(xiàn)，如下：>>b5.name ='New name'>>b5.save()

保存外鍵和多對多關(guān)系的字段偿乖，如下例子：

更新外鍵字段和普通的字段一樣击罪，只要指定一個(gè)對象的正確類型。>>>cheese_blog = Blog.objects.get (name="Cheddar Talk")>>>entry.blog = cheese_blog>>>entry.save()

更新多對多字段時(shí)又一點(diǎn)不太一樣贪薪，使用add()方法添加相關(guān)聯(lián)的字段的值媳禁。>>joe = Author.objects.create(name="Joe")>>entry.authors.add(joe)

三、檢索對象>>>Blog.objects

>>>b = Blog(name='Foo', tagline='Bar')>>>b.objectsTraceback:...AttributeError:"Manager isn't accessible via Blog instances."1画切、檢索所有的對象>>>all_entries = Entry.objects.all()

使用all()方法返回?cái)?shù)據(jù)庫中的所有對象竣稽。2、檢索特定的對象

使用以下兩個(gè)方法：

fileter(**kwargs)

返回一個(gè)與參數(shù)匹配的QuerySet,相當(dāng)于等于(=).

exclude(**kwargs)

返回一個(gè)與參數(shù)不匹配的QuerySet,相當(dāng)于不等于(!=)。

Entry.objects.filter (pub_date__year=2006)

不使用Entry.objects.all().filter (pub_date__year=2006),雖然也能運(yùn)行毫别，all()最好再獲取所有的對象時(shí)使用娃弓。

上面的例子等同于的sql語句：

slect *fromentry where pub_date_year='2006'鏈接過濾器：>>>Entry.objects.filter (...headline__startswith='What'...).exclude(...pub_date__gte=datetime.now()...).filter (...pub_date__gte=datetime(2005,1,1)...)

最后返回的QuerySet是headline like'What%'andput_date2005-01-01另外一種方法：>>q1 = Entry.objects.filter (headline__startswith="What")>>q2 = q1.exclude(pub_date__gte=datetime.now())>>q3 = q1.filter (pub_date__gte=datetime.now())

這種方法的好處是可以對q1進(jìn)行重用。

QuerySet是延遲加載

只在使用的時(shí)候才會(huì)去訪問數(shù)據(jù)庫岛宦，如下：>>>q = Entry.objects.filter (headline__startswith="What")>>>q = q.filter (pub_date__lte=datetime.now())>>>q = q.exclude(body_text__icontains="food")>>>printq

在printq時(shí)才會(huì)訪問數(shù)據(jù)庫台丛。

其他的QuerySet方法>>>Entry.objects.all()[:5]

這是查找前5個(gè)entry表里的數(shù)據(jù)>>>Entry.objects.all()[5:10]

這是查找從第5個(gè)到第10個(gè)之間的數(shù)據(jù)。>>>Entry.objects.all()[:10:2]

這是查詢從第0個(gè)開始到第10個(gè)砾肺，步長為2的數(shù)據(jù)挽霉。>>>Entry.objects.order_by('headline')[0]

這是取按headline字段排序后的第一個(gè)對象。>>>Entry.objects.order_by('headline')[0:1].get ()

這和上面的等同的债沮。>>>Entry.objects.filter (pub_date__lte='2006-01-01')

等同于SELECT * FROM blog_entry WHERE pub_date <='2006-01-01';>>>Entry.objects.get (headline__exact="Man bites dog")

等同于SELECT ... WHERE headline ='Man bites dog';>>>Blog.objects.get (id__exact=14)# Explicit form>>>Blog.objects.get (id=14)# __exact is implied這兩種方式是等同的炼吴，都是查找id=14的對象。>>>Blog.objects.get (name__iexact="beatles blog")

查找name="beatles blog"的對象疫衩，不去飯大小寫硅蹦。

Entry.objects.get (headline__contains='Lennon')

等同于SELECT ... WHERE headline LIKE'%Lennon%';

startswith 等同于sql語句中的 name like'Lennon%',

endswith等同于sql語句中的 name like'%Lennon'.>>>Entry.objects.filter (blog__name__exact='Beatles Blog')

查找entry表中外鍵關(guān)系blog_name='Beatles Blog'的Entry對象。>>>Blog.objects.filter (entry__headline__contains='Lennon')

查找blog表中外鍵關(guān)系entry表中的headline字段中包含Lennon的blog數(shù)據(jù)闷煤。

Blog.objects.filter (entry__author__name='Lennon')

查找blog表中外鍵關(guān)系entry表中的author字段中包含Lennon的blog數(shù)據(jù)童芹。

Blog.objects.filter (entry__author__name__isnull=True)

Blog.objects.filter (entry__author__isnull=False,entry__author__name__isnull=True)

查詢的是author_name為null的值

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

Blog.objects.filter (entry__headline__contains='Lennon').filter (? entry__pub_date__year=2008)

這兩種查詢在某些情況下是相同的，某些情況下是不同的鲤拿。第一種是限制所有的blog數(shù)據(jù)的假褪，而第二種情況則是第一個(gè)filter 是

限制blog的，而第二個(gè)filter 則是限制entry的>>>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等同于select *fromwhere id=14# Get blogs entries with id 1, 4 and 7>>>Blog.objects.filter (pk__in=[1,4,7])

等同于select *fromwhere idin{1,4,7}# Get all blog entries with id > 14>>>Blog.objects.filter (pk__gt=14)

等同于select *fromid>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'%\%%';

CachingandQuerySets>>>print[e.headlineforeinEntry.objects.all()]>>>print[e.pub_dateforeinEntry.objects.all()]

應(yīng)改寫為：>>queryset = Poll.objects.all()>>>print[p.headlineforpinqueryset]# Evaluate the query set.>>>print[p.pub_dateforpinqueryset]# Re-use the cache from the evaluation.近顷、這樣利用緩存生音，減少訪問數(shù)據(jù)庫的次數(shù)。

四窒升、用Q對象實(shí)現(xiàn)復(fù)雜的查詢

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 *frompolls WHERE question LIKE'Who%'AND (pub_date ='2005-05-02'OR pub_date ='2005-05-06')

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

五缀遍、比較對象>>>some_entry == other_entry>>>some_entry.id == other_entry.id

六、刪除

Entry.objects.filter (pub_date__year=2005).delete()

b = Blog.objects.get (pk=1)# This will delete the Blog and all of its Entry objects.b.delete()

Entry.objects.all().delete()

刪除所有

七饱须、一次更新多個(gè)值# Update all the headlines with pub_date in 2007.Entry.objects.filter (pub_date__year=2007).update(headline='Everything is the same')>>>b = Blog.objects.get (pk=1)# Change every Entry so that it belongs to this Blog.>>>Entry.objects.all().update(blog=b)

如果用save()方法域醇，必須一個(gè)一個(gè)進(jìn)行保存，需要對其就行遍歷蓉媳，如下：foriteminmy_queryset:item.save()

關(guān)聯(lián)對象

one-to-many>>>e = Entry.objects.get (id=2)>>>e.blog# Returns the related Blog object.>>>e = Entry.objects.get (id=2)>>>e.blog = some_blog>>>e.save()>>>e = Entry.objects.get (id=2)>>>e.blog =None>>>e.save()# "UPDATE blog_entry SET blog_id = NULL ...;">>>e = Entry.objects.get (id=2)>>>printe.blog# Hits the database to retrieve the associated Blog.>>>printe.blog# Doesn't hit the database; uses cached version.>>>e = Entry.objects.select_related().get (id=2)>>>printe.blog# Doesn't hit the database; uses cached version.>>>printe.blog# Doesn't hit the database; uses cached version>>>b = Blog.objects.get (id=1)>>>b.entry_set.all()# Returns all Entry objects related to Blog.# b.entry_set is a Manager that returns QuerySets.>>>b.entry_set.filter (headline__contains='Lennon')>>>b.entry_set.count()>>>b = Blog.objects.get (id=1)>>>b.entries.all()# Returns all Entry objects related to Blog.# b.entries is a Manager that returns QuerySets.>>>b.entries.filter (headline__contains='Lennon')>>>b.entries.count()

You cannot access a reverse ForeignKey Managerfromtheclass;itmustbeaccessedfromaninstance:>>>Blog.entry_set

add(obj1, obj2, ...)

Adds the specified model objects to the related object set.

create(**kwargs)

Creates a new object, saves itandputs itinthe related object set. Returns the newly created object.

remove(obj1, obj2, ...)

Removes the specified model objectsfromthe related object set.

clear()

Removes all objectsfromthe related object set.

many-to-many類型：

e = Entry.objects.get (id=3)

e.authors.all()# Returns all Author objects for this Entry.e.authors.count()

e.authors.filter (name__contains='John')

a = Author.objects.get (id=5)

a.entry_set.all()

# Returns all Entry objects for this Author.one-to-one 類型：

class EntryDetail(models.Model):

entry = models.OneToOneField(Entry)

details = models.TextField()

ed = EntryDetail.objects.get (id=2)

ed.entry

# Returns the related Entry object使用sql語句進(jìn)行查詢：

def my_custom_sql(self):

from django .db import connection

cursor = connection.cursor()

cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])

row = cursor.fetchone()

return row

link:python--django database grammar - Bing- 的個(gè)人空間 - 開源中國社區(qū)