Django------Making queries 查询

    Once you've created your date models, Django automatically gives you a database-abstraction API that lets you create, retrieve,update and delete objects. This document explains how to use this API .

   Refer to the following models, which comprise a Weblog application : /mysit/blog/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()

    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()
    mod_date = models.DateTimeField()
    authors = models.ManyToManyField(Author)
    n_comments = models.IntegerField()
    n_pingbacks = models.IntegerField()
    rating = models.IntegerField()

    def __unicode__(self):
        return self.headline

Creating objects:

To represent database-table data in Python objects, Django use an intuitive(直观) system:  A model class represent a database table, and an instance of that class represents a paticular record in the database table.

To create an object, instantiate it using keyword arguments to the model class, then call save() to save it to the database.

Here's an example:

$ python manage.py shell
>>>from blog.models import Blog
>>>b = Blog(name='Beatle Blog', tagline='All the latest Beatles News')
>>>b.save()
>>>print b
Beatle Blog

This performs(执行) an INSERT SQL statement behind the scenes. Django doesn't hit the database until you explicitly(明确) call save().

Saving changes to objects:

>>>b.name="Beatles's Blog"
>>>b.save()

To save changes to an object that's already in the database , use save().

This performs an UPDATE SQL statement behind the scenes. Django does't hit the database until you explicitly call save().

Saving ForeignKey and ManyToManyField fields:

Updating a ForeignKey field works exactly the same way as saving a normal field -- simple assign(分配)an object of the right type  to the field in question.

The exmple update the blog attribute of an Entry instance entry:

>>>from blog.models import Entry
>>>entry = Entry.objects.get(pk=1)
>>>chess_blog = Blog.objects.get(name='chess talk')
>>>entry.blog = chess_blog
>>>entry.save()

Update a ManyToManyField works a little differently -- use the add() method on the field to add a record to the relation. This exmple adds the Author instance joe to the entry object:

>>>from blog.models import Author
>>>joe = Author.objects.create(name="Joe")
>>>entry.author.add(joe)

To add multiple records to a ManyToManyField in one go, include multiple arguments in the call to add():

>>> john = Author.objects.create(name="John")
>>> paul = Author.objects.create(name="Paul")
>>> george = Author.objects.create(name="George")
>>> ringo = Author.objects.create(name="Ringo")
>>>entry.author.add(john,paul,george,ringo)

Django will complain if you try to assign or add an object of the wrong type.

Retrieving objects:

To retrieve objects from your database, construct a QuerySet via a Manager on your models class.

A QuerySet represent a collection of objects from your database. it can have zero, one or many filters -- criteria(标准) that narrow down(缩小) the collection based on given parameters.In SQL termsm, a QuerySet equates to a SELECT statement, and a filter is a limiting clause such as WHERE or LIMIT.

You get a QuerySet by using your model's Manager. Each model has at least one Manager, and it's called objects by default. Access it direct via the model class, like so:

>>> Blog.objects
<django.db.models.manager.Manager object at 0x8ae69cc>
>>> b=Blog(name='foo', tagline='bar')
>>> b.objects
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.7/site-packages/django/db/models/manager.py", line 219, in __get__
    raise AttributeError("Manager isn't accessible via %s instances" % type.__name__)
AttributeError: Manager isn't accessible via Blog instances

Managers are accessible only via model classes, rather than from model instance, to enforce(执行) a separation between "table-level" operations and "record-level" operations.

The Manager is the main source of QuerySet for a model. It act as a 'root' QuerySet that describes all objects in the model's database table. For example, Blog.objects is the initial QuerySet that contains all Blog objects in  the database.

Retrieving all objects:use the all() method on a Manager:

>>>all_entries = Entry.objects.all()

The all() method returns a QuerySet of all the objects in the database.

Retrieving specify objects with filters:

To create such a subset , you refine(提炼) the initial QuerySet, adding filter conditions. The two most comman ways to refine a QuerySet are:

>>>Entry.objects.filter(pub_date__year=2006)
>>>Entry.objects.all().filter(...)
That would still work, but you only need all() when you want all objects from root QuerySet

exclude(**kwargs)
Return a new QuerySet containing onjects that do not match the given lookup parameters.

The parameters should be in the format described in Field lookups below.

Chaining filters:

The result of refining a QuerySet is itself a QuerySet, so it's possible to chain refinements together.For example:

>>>Entry.objects.filter(headline__startswith='What').exclude(pub_date__gte=datetime.now()).filter(pub_date__gte=datetime(2005, 1, 1))

This takes the initial QuerySet of all entries in the database, adds a filter then an exclusion, then another filter. The final result is a QuerySet containing all entries with a headline that starts with "What", that wer published between January 1, 2005, and the current day.

Filtered QuerySet are unique:

>>>q1 = Entry.objects.filter(head_line__startwith="What")
>>>q2 = q1.exclude(...)
>>>q3 = q1.filter(...)

These three QuerySet are separate. The first is a base QuerySet containing all entries that contain a headline starting with "What". The second is a subset of the first.  Tje third is a subset of the first, The initial QuerySet is unaffected by the refinement process.

QuerySet art lazy:

The act of creating QuerySet does not involve any database activity. you can stack filter together all day long, and Django won't actually run query until the QuerySet is evaluated. Look these example:

>>> q = Entry.objects.filter(headline__startswith="What")
>>> q = q.filter(pub_date__lte=datetime.now())
>>> q = q.exclude(body_text__icontains="food")
>>> print q

In fact, it hits the database only once, at the last line. In general, the results of a QuerySet aren't fetch from the database until you "ask" for them. When you do, the QuerySet is evaluated by accessing the database.

Retrieving a single object with get:

filter() will always give you a QuerySet, even if only a single object matches the query- in this case , it will be a QuerySet that containing a single element. if you kown there is only one object that matches your query, you can use the get() method on a Manager which returns the object directly:

>>> one_object = Entry.objects.get(pk=1)

You can use any query expression with get(), just like with filter().

Django will complain if more than one item matches the get() query.it will raise MultipleObjectsReturned, which is an attribute of the model class itself.

 

Most of the time we will use all() get() filter() and exclude() when you need to look up objects from the database. However, that's far froom all  there is; see the Queryset API Reference.

Limiting QuerySets:

Use a subset of Python's array-slicing syntax to limit your QuerySet to a certain number of results.
>>>Entry.objects.all()[:5]
>>>Entry.objects.all()[5:10]
>>>Entry.objects.all()[-1] is not supported.
Generally, slicing a QuerySet returns a new QuerySet -- it doesn't evaluate the query. an exception is if you use the 'step' parameter of Python slice syntax, for example:this would actually execute the query in order to return a list of every second object of the first 10.
>>>Entry.objects.all()[:10:2]

To retrieve a single object rather than a list, use a simple index instead of a slice. For example, this returns the first Entry in the database, after ordering entries alphabetically by headline:

>>>Entry.objects.order_by('headline')[0]
This is roughly equivalent to :
>>>Entry.objects.order_by('headline')[0:1].get()
Note, however, that the first of these will raise IndexError while the second will raise DoesNotExist if no objects match the given criteria(标准).

Field lookups:

Field lookups are how you specify the meat of an SQL WHERE clause. They're specified as keyword arguments to the QuerySet methods filter(),exclude() and get().

Basic lookups keyword arguments take the form field__lookuptype=value.

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

translates(roughly) into following SQL:

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

The field specified in a lookup has to be the name of a model field. There's one exception though, in case of a foreignkey you can specify the field name suffixed with _id. in this case, the value parameter is expected to contain the raw value of the foreign models's primary key,For example:

>>>Entry.objects.filter(blog_id_exact=4)

exact:

The following two statements are equivalent:
>>>Blog.objects.get(id__exact=14)
>>>Blog.objects.get(id=14)
This is for convenience（方便）,because exact lookups are the common case. 

iexact: A case-insensitive match.

contains:

icontains:

startswith, endswith

istartswith,endswith

Lookups that span(持续) relationships:

Django offers a powerful and intuitive(直观) way to "follow" relationships in lookups, taking care of the SQL JOINs for you automatically, behind the scense. To span a relationship, just use the field name of related fields across models, separated by double underscores, until you get to the field you want.

This example retrieve all Entry objects with a Blog whose name is 'Beatles Blog':

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

This span can be as deep as you'd like.

It works backwards, too. To refer to a "reverse" relationship, just use the lowercase name of the model.

>>>Blog.objects.filter(entry__name__contains='Lennon')

Spanning multi-valued relationships:

To select all blogs that contain entries with both 'Lennon' in the headline and that were published in 2008(the same entry satisfying both conditions),we would write:

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

To select all blogs that contain an entry with "Lennon" in the headlines as well as an entry that was published in 2008, we would write:

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

In this second example, the first filter restricted(限制) the queryset to all those blogs linked to that particular type of entry(条目).The second filter restricted the set of blogs further to those that are also linked to the second type of entry.The entries select by the second filter may not be the same as the entries in the first filter.We are filtering the Blog items with each filter statement, not the Entry items.

Filter can reference fields on the model:

Instances of F() act as a reference to a model field within a query.

To find a list of all blog entries tha have had more conmments than pingbacks, we construct an F() object to reference the pingback count, and use that F() object in the query:

>>>from django.db.modles import F
>>>Entry.objects.filter(n_comments__gt=F('n_pingbacks'))

Django supports the use of addition , substraction , multiplication , division and modulo arithmetic with F() objects.

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

To retrieve all the entries where the author's name is the same as the blog name, we could issue the query(发出查询):

>>>Entry.objects.filter(authors__name=F('blog__name'))

The pk lookup shortcut:

For convenience, Django provides a pk lookup shortcut, which stands for 'primary key'

These three statements are equivalent:

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

Escaping percent signs and underscores in LIKE statements:

>>> Entry.objects.filter(headline__contains='%')

Same goes for underscores.Both percentage signs and unserscores are handled for you transparently.

Catching and QuerySets:
Each QuerySet contains a catche, to minimize database access. It's important to unserstand how it works, in order to write the most efficient code.

In a newly created QuerySet, the cache is empty. The first time a QuerySet is evaluated -- and hence, a database query happens--Django saves the query results in the QuerySet's cache and returns the results that have been explicitly requested Subsequent evaluations of the QuerySet reuse the cached results.

>>> print [e.headline for e in Entry.objects.all()]
>>> print [e.pub_date for e in Entry.objects.all()]

That means the same database query will be executed twice, effectively doubling your database load. Also, there's a possibility the two lists may not include the same database records.To avoid  this problem, simply save the QuerySet and reuse it:

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

Complex lookup with Q objects:

>>>from django.db.models import Q
>>>Q(question__startswith='What')

When an operator is used on two Q object ,it yields a new Q object.

"OR"

Q(question__startswith='Who')|Q(question__startswith='What')
#This equivalent to the following SQL WHERE clause:
WHERE question LIKE 'who%' OR question LIKE 'What%'

Allowing for conbined lookups that conbine both a normal query and a negated query:

Q(question__startswith='Who')|~Q(pub_date__year=2005)

If you provide multiple Q object arguments to lookup function, the arguments will be "AND"ed together. For example:

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

If a Q object is provided, it musst precede the definition of any keyword arguments. For example:

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

would be a valid query, equivalent to the previous example; but:

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

would not be valid.

Comparing objects:

To compare two model instances, just use "==", Behind the scenes, that compares the primary key values of two models.

The following two statements are equivalent:

>>>some_entry == other_entry
>>>some_entry.id == other_entry.id

If a model's primary key field is called name, these two statements are equivalent:

>>> some_obj == other_obj
>>> some_obj.name == other_obj.name

Deleting objects:

The delete method, conveniently, is named delete().This method immediately deletes the object and has no return value. Example:

e.delete()

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

b=Blog.objects.get(pk=1)
b.delete()

#This will delete the Blog and all of its Entry objects.

Note that delete() is the only QuerySet method that is not exposed on a Manager itself. This is a safety mechanism to prevent you from accidentally requesting Entry.objects.delete(), and deleting all the entrys. if you do want to delete all objects, then you have to explicitly(显示) request a complete query set:

Entry.objects.all().delete()

Copying model instances:

Althougn there is no built-in method for copying model instances, it is possible to easily create new instance with all fields'values copied, IN the simplest case, you can just set pk to None. Using our blog example:

blog = Blog(name='My blog', tagline='Blog sha')
blog .save() # pk==1

blog.pk=None
blog.save() # pk==2

Things get more complicated if you use inheritance, Consider a subclass of Blog:

class ThemeBlog(Blog):
   theme = models.CharField(max_length=200)

django_blog = ThemeBlog(name='Django', tabline='shabi')
django_blog.save()# django_blog.pk == 3

Due to how inheritance works, you have to set both pk and id to None:

django_blog.pk = None
django_blog.id = None
django_blog.save() # django_blog.pk == 4

This process does not copy related objects. If you want to copy relations, you have to write a little bit more code. In our example, Entry has a many to many field to Author:

entry = Entry.objects.all()[0]
old_author = Author.object.all()
entry.pk = None
entry.save()
entry.authors = old_author # save new many2many relations

Updating multiple objects at once:

Sometimes you want to set a field to a particular value for all the objects in a QuerySet.You can do this with the update() method.

# Update all the headlines with pub_date in 2007.
Entry.objects.filter(pub_date__year=2007).update(headline='Every')

You can only set non-relation fields and ForeignKey fields using this method. To update a non-relation field, provide the new value as a constant. To update ForeignKey fields, set the new value to be the new model instance you want to point to:

b = Blog.objects.get(pk=1)
# Change every Entry so that it belongs to this Blog.
Entry.objects.all().update(blog=b)

The update() method is applied instantly and returns the number of rows matched by the query.

>>> b = Blog.objects.get(pk=1)

# Update all the headlines belonging to this Blog.
>>>Entry.objects.select_related().filter(blog=b).update(headline='head')

If you want to save every item in a QuerySet and make sure that the save() method is called on each instance, you do not need any special function to handle that.Just loop over them and call save():

for item in my_queryset
    item.save()

>>> Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)

# THIS WILL RAISE A FieldError
>>> Entry.objects.update(headline=F('blog__name'))