本篇主要介紹我們?cè)谑褂胐jango框架開發(fā)的時(shí)候使用的操作數(shù)據(jù)庫的語句备燃，這在動(dòng)態(tài)網(wǎng)站部分是很重要的辅鲸，因?yàn)槲覀冃枰B接數(shù)據(jù)庫進(jìn)行操作治筒，然后把數(shù)據(jù)處理的結(jié)果顯示在前端頁面上酱床，在其他地方我們都是直接用sql語句來手動(dòng)輸入羊赵，這樣容易造成錯(cuò)誤，還影響效率扇谣，在django中昧捷，內(nèi)置了很多處理數(shù)據(jù)庫操作的函數(shù)，能更便利的幫我們操作數(shù)據(jù)庫

開始

實(shí)例代碼

models.py

class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()

def __unicode__(self):
    return self.name

class Author(models.Model):
    name = models.CharField(max_length=50)
    email = models.EmailField()

#python3中用__str__
def __unicode__(self):
    return self.name


class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateTimeField()

authors = models.ManyToManyField(Author)

#python3中用__str__
def __unicode__(self):
    return self.headline 
 

• models模塊中有三個(gè)類blog罐寨，author靡挥，以及entry
• entry分別與blog與author表關(guān)聯(lián)
• entry與blog表是通過外鍵(models.ForeignKey())相連，屬于一對(duì)多的關(guān)系鸯绿，即一個(gè)entry對(duì)應(yīng)多個(gè)blog跋破，entry與author是多對(duì)多的關(guān)系，通過modles.ManyToManyField()實(shí)現(xiàn)

插入數(shù)據(jù)庫

• 用save()方法實(shí)現(xiàn)瓶蝴，如下所示：

from mysite.blog.models import Blog  #導(dǎo)入blog這個(gè)app

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

保存外鍵和多對(duì)多關(guān)系的字段

• 更新外鍵字段和普通的字段一樣，只要指定一個(gè)對(duì)象的正確類型舷手。

 cheese_blog = Blog.objects.get(name="Cheddar Talk")
 entry.blog = cheese_blog
 entry.save()

更新多對(duì)多字段時(shí)又一點(diǎn)不太一樣拧簸，使用add()方法添加相關(guān)聯(lián)的字段的值

 joe = Author.objects.create(name="Joe")
 entry.authors.add(joe)

檢索對(duì)象

檢索所有的對(duì)象

• 使用all()方法返回?cái)?shù)據(jù)庫中的所有對(duì)象。

all_entries = Entry.objects.all()

檢索特定的對(duì)象

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

#fileter(**kwargs)
Entry.objects.filter(pub_date__year=2006)

• 以上語句等同于slect * from entry where pub_date_year='2006'

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

exclude(**kwargs)

對(duì)參數(shù)進(jìn)行重用的方法

 q1 = Entry.objects.filter(headline__startswith="What")
 q2 = q1.exclude(pub_date__gte=datetime.now())
 q3 = q1.filter(pub_date__gte=datetime.now())

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")
  print q

• 在print q時(shí)才會(huì)訪問數(shù)據(jù)庫

查找前5個(gè)entry表里的數(shù)據(jù)

  Entry.objects.all()[:5]

查找從第5個(gè)到第10個(gè)之間的數(shù)據(jù)

  Entry.objects.all()[5:10]

查詢從第0個(gè)開始到第10個(gè)男窟，步長(zhǎng)為2的數(shù)據(jù)

  Entry.objects.all()[:10:2]

取按headline字段排序后的第一個(gè)對(duì)象

   Entry.objects.order_by('headline')[0]
   #或Entry.objects.order_by('headline')[0:1].get()

查找id=14的對(duì)象盆赤。

    Blog.objects.get(id__exact=14) # Explicit form
    Blog.objects.get(id=14)         # __exact is implied

SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01'

     Entry.objects.filter(pub_date__lte='2006-01-01')

SELECT ... WHERE headline = 'Man bites dog'

      Entry.objects.get(headline__exact="Man bites dog")

查找name="beatles blog"的對(duì)象，不區(qū)分大小寫蝎宇。

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

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

查找entry表中外鍵關(guān)系blog_name='Beatles Blog'的Entry對(duì)象

     Entry.objects.filter(blog__name__exact='Beatles Blog')

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

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

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

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

查詢的是author_name為null的值

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

select * from where id=14

    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 * from where id in{1,4,7}

    Blog.objects.filter(pk__in=[1,4,7])

select * from id>14

    Blog.objects.filter(pk__gt=14)

SELECT ... WHERE headline LIKE '%%%'

    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

用Q對(duì)象實(shí)現(xiàn)復(fù)雜的查詢

WHERE question LIKE 'Who%' OR question LIKE 'What%'

    Q(question__startswith='Who') | Q(question__startswith='What')

SELECT * from polls WHERE question LIKE 'Who%' AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

Poll.objects.get(
        Q(question__startswith='Who'),
        Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
    )   

Poll.objects.get(question__startswith='Who', Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))

   Poll.objects.get(
       Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
       question__startswith='Who')

比較對(duì)象

比較

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

Change every Entry so that it belongs to this Blog

b = Blog.objects.get(pk=1)
Entry.objects.all().update(blog=b)

• 但是如果用save()方法弟劲，必須一個(gè)一個(gè)進(jìn)行保存，需要對(duì)其就行遍歷姥芥，如下：

for item in my_queryset:
    item.save()

其他

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

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)
print e.blog # Hits the database to retrieve the associated Blog.
print e.blog # Doesn't hit the database; uses cached version.

e = Entry.objects.select_related().get(id=2)
print e.blog # Doesn't hit the database; uses cached version.
print e.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 Manager from the class; it must be accessed from an instance:
Blog.entry_set

add(obj1, obj2, ...)
Adds the specified model objects to the related object set.
create(**kwargs)
Creates a new object, saves it and puts it in the related object set. Returns the newly created object.
remove(obj1, obj2, ...)
Removes the specified model objects from the related object set.
clear()
Removes all objects from the 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