Django操作数据库

本篇主要介绍我们在使用django框架开发的时候使用的操作数据库的语句，这在动态网站部分是很重要的，因为我们需要连接数据库进行操作，然后把数据处理的结果显示在前端页面上，在其他地方我们都是直接用sql语句来手动输入，这样容易造成错误，还影响效率，在django中，内置了很多处理数据库操作的函数，能更便利的帮我们操作数据库

开始

实例代码

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模块中有三个类blog，author，以及entry
• entry分别与blog与author表关联
• entry与blog表是通过外键(models.ForeignKey())相连，属于一对多的关系，即一个entry对应多个blog，entry与author是多对多的关系，通过modles.ManyToManyField()实现

插入数据库

• 用save()方法实现，如下所示：

from mysite.blog.models import Blog  #导入blog这个app

b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
b.save()

更新数据库

• 用save()方法实现，如下所示：

 b5.name = 'New name
 b5.save()

保存外键和多对多关系的字段

• 更新外键字段和普通的字段一样，只要指定一个对象的正确类型。

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

更新多对多字段时又一点不太一样，使用add()方法添加相关联的字段的值

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

检索对象

检索所有的对象

• 使用all()方法返回数据库中的所有对象。

all_entries = Entry.objects.all()

检索特定的对象

返回一个与参数匹配的QuerySet,相当于等于(=)

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

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

返回一个与参数不匹配的QuerySet,相当于不等于(!=)

exclude(**kwargs)

对参数进行重用的方法

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

QuerySet是延迟加载(只在使用的时候才会去访问数据库)

  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时才会访问数据库

查找前5个entry表里的数据

  Entry.objects.all()[:5]

查找从第5个到第10个之间的数据

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

查询从第0个开始到第10个，步长为2的数据

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

取按headline字段排序后的第一个对象

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

查找id=14的对象。

    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"的对象，不区分大小写。

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

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

查找entry表中外键关系blog_name='Beatles Blog'的Entry对象

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

查找blog表中外键关系entry表中的headline字段中包含Lennon的blog数据

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

查找blog表中外键关系entry表中的author字段中包含Lennon的blog数据

      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对象实现复杂的查询

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

比较对象

比较

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

一次更新多个值

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()方法，必须一个一个进行保存，需要对其就行遍历，如下：

for item in my_queryset:
    item.save()

其他

关联对象

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语句进行查询：

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