PageRenderTime 48ms CodeModel.GetById 18ms app.highlight 9ms RepoModel.GetById 1ms app.codeStats 1ms

/docs/topics/db/queries.txt

https://code.google.com/p/mango-py/
Plain Text | 1120 lines | 793 code | 327 blank | 0 comment | 0 complexity | ffd6d0ed34c03e6625013130e49fcda8 MD5 | raw file
   1==============
   2Making queries
   3==============
   4
   5.. currentmodule:: django.db.models
   6
   7Once you've created your :doc:`data models </topics/db/models>`, Django
   8automatically gives you a database-abstraction API that lets you create,
   9retrieve, update and delete objects. This document explains how to use this
  10API. Refer to the :doc:`data model reference </ref/models/index>` for full
  11details of all the various model lookup options.
  12
  13Throughout this guide (and in the reference), we'll refer to the following
  14models, which comprise a Weblog application:
  15
  16.. _queryset-model-example:
  17
  18.. code-block:: python
  19
  20    class Blog(models.Model):
  21        name = models.CharField(max_length=100)
  22        tagline = models.TextField()
  23
  24        def __unicode__(self):
  25            return self.name
  26
  27    class Author(models.Model):
  28        name = models.CharField(max_length=50)
  29        email = models.EmailField()
  30
  31        def __unicode__(self):
  32            return self.name
  33
  34    class Entry(models.Model):
  35        blog = models.ForeignKey(Blog)
  36        headline = models.CharField(max_length=255)
  37        body_text = models.TextField()
  38        pub_date = models.DateTimeField()
  39        mod_date = models.DateTimeField()
  40        authors = models.ManyToManyField(Author)
  41        n_comments = models.IntegerField()
  42        n_pingbacks = models.IntegerField()
  43        rating = models.IntegerField()
  44
  45        def __unicode__(self):
  46            return self.headline
  47
  48Creating objects
  49================
  50
  51To represent database-table data in Python objects, Django uses an intuitive
  52system: A model class represents a database table, and an instance of that
  53class represents a particular record in the database table.
  54
  55To create an object, instantiate it using keyword arguments to the model class,
  56then call ``save()`` to save it to the database.
  57
  58You import the model class from wherever it lives on the Python path, as you
  59may expect. (We point this out here because previous Django versions required
  60funky model importing.)
  61
  62Assuming models live in a file ``mysite/blog/models.py``, here's an example::
  63
  64    >>> from blog.models import Blog
  65    >>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
  66    >>> b.save()
  67
  68This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit
  69the database until you explicitly call ``save()``.
  70
  71The ``save()`` method has no return value.
  72
  73.. seealso::
  74
  75    ``save()`` takes a number of advanced options not described here.
  76    See the documentation for ``save()`` for complete details.
  77
  78    To create an object and save it all in one step see the ```create()```
  79    method.
  80
  81Saving changes to objects
  82=========================
  83
  84To save changes to an object that's already in the database, use ``save()``.
  85
  86Given a ``Blog`` instance ``b5`` that has already been saved to the database,
  87this example changes its name and updates its record in the database::
  88
  89    >> b5.name = 'New name'
  90    >> b5.save()
  91
  92This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit
  93the database until you explicitly call ``save()``.
  94
  95Saving ``ForeignKey`` and ``ManyToManyField`` fields
  96----------------------------------------------------
  97
  98Updating a ``ForeignKey`` field works exactly the same way as saving a normal
  99field; simply assign an object of the right type to the field in question.
 100This example updates the ``blog`` attribute of an ``Entry`` instance ``entry``::
 101
 102    >>> from blog.models import Entry
 103    >>> entry = Entry.objects.get(pk=1)
 104    >>> cheese_blog = Blog.objects.get(name="Cheddar Talk")
 105    >>> entry.blog = cheese_blog
 106    >>> entry.save()
 107
 108Updating a ``ManyToManyField`` works a little differently; use the ``add()``
 109method on the field to add a record to the relation. This example adds the
 110``Author`` instance ``joe`` to the ``entry`` object::
 111
 112    >>> from blog.models import Author
 113    >>> joe = Author.objects.create(name="Joe")
 114    >>> entry.authors.add(joe)
 115
 116Django will complain if you try to assign or add an object of the wrong type.
 117
 118Retrieving objects
 119==================
 120
 121To retrieve objects from your database, you construct a ``QuerySet`` via a
 122``Manager`` on your model class.
 123
 124A ``QuerySet`` represents a collection of objects from your database. It can
 125have zero, one or many *filters* -- criteria that narrow down the collection
 126based on given parameters. In SQL terms, a ``QuerySet`` equates to a ``SELECT``
 127statement, and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``.
 128
 129You get a ``QuerySet`` by using your model's ``Manager``. Each model has at
 130least one ``Manager``, and it's called ``objects`` by default. Access it
 131directly via the model class, like so::
 132
 133    >>> Blog.objects
 134    <django.db.models.manager.Manager object at ...>
 135    >>> b = Blog(name='Foo', tagline='Bar')
 136    >>> b.objects
 137    Traceback:
 138        ...
 139    AttributeError: "Manager isn't accessible via Blog instances."
 140
 141.. note::
 142
 143    ``Managers`` are accessible only via model classes, rather than from model
 144    instances, to enforce a separation between "table-level" operations and
 145    "record-level" operations.
 146
 147The ``Manager`` is the main source of ``QuerySets`` for a model. It acts as a
 148"root" ``QuerySet`` that describes all objects in the model's database table.
 149For example, ``Blog.objects`` is the initial ``QuerySet`` that contains all
 150``Blog`` objects in the database.
 151
 152Retrieving all objects
 153----------------------
 154
 155The simplest way to retrieve objects from a table is to get all of them.
 156To do this, use the ``all()`` method on a ``Manager``::
 157
 158    >>> all_entries = Entry.objects.all()
 159
 160The ``all()`` method returns a ``QuerySet`` of all the objects in the database.
 161
 162(If ``Entry.objects`` is a ``QuerySet``, why can't we just do ``Entry.objects``?
 163That's because ``Entry.objects``, the root ``QuerySet``, is a special case
 164that cannot be evaluated. The ``all()`` method returns a ``QuerySet`` that
 165*can* be evaluated.)
 166
 167
 168Retrieving specific objects with filters
 169----------------------------------------
 170
 171The root ``QuerySet`` provided by the ``Manager`` describes all objects in the
 172database table. Usually, though, you'll need to select only a subset of the
 173complete set of objects.
 174
 175To create such a subset, you refine the initial ``QuerySet``, adding filter
 176conditions. The two most common ways to refine a ``QuerySet`` are:
 177
 178    ``filter(**kwargs)``
 179        Returns a new ``QuerySet`` containing objects that match the given
 180        lookup parameters.
 181
 182    ``exclude(**kwargs)``
 183        Returns a new ``QuerySet`` containing objects that do *not* match the
 184        given lookup parameters.
 185
 186The lookup parameters (``**kwargs`` in the above function definitions) should
 187be in the format described in `Field lookups`_ below.
 188
 189For example, to get a ``QuerySet`` of blog entries from the year 2006, use
 190``filter()`` like so::
 191
 192    Entry.objects.filter(pub_date__year=2006)
 193
 194We don't have to add an ``all()`` -- ``Entry.objects.all().filter(...)``. That
 195would still work, but you only need ``all()`` when you want all objects from the
 196root ``QuerySet``.
 197
 198.. _chaining-filters:
 199
 200Chaining filters
 201~~~~~~~~~~~~~~~~
 202
 203The result of refining a ``QuerySet`` is itself a ``QuerySet``, so it's
 204possible to chain refinements together. For example::
 205
 206    >>> Entry.objects.filter(
 207    ...     headline__startswith='What'
 208    ... ).exclude(
 209    ...     pub_date__gte=datetime.now()
 210    ... ).filter(
 211    ...     pub_date__gte=datetime(2005, 1, 1)
 212    ... )
 213
 214This takes the initial ``QuerySet`` of all entries in the database, adds a
 215filter, then an exclusion, then another filter. The final result is a
 216``QuerySet`` containing all entries with a headline that starts with "What",
 217that were published between January 1, 2005, and the current day.
 218
 219.. _filtered-querysets-are-unique:
 220
 221Filtered QuerySets are unique
 222~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 223
 224Each time you refine a ``QuerySet``, you get a brand-new ``QuerySet`` that is
 225in no way bound to the previous ``QuerySet``. Each refinement creates a
 226separate and distinct ``QuerySet`` that can be stored, used and reused.
 227
 228Example::
 229
 230    >> q1 = Entry.objects.filter(headline__startswith="What")
 231    >> q2 = q1.exclude(pub_date__gte=datetime.now())
 232    >> q3 = q1.filter(pub_date__gte=datetime.now())
 233
 234These three ``QuerySets`` are separate. The first is a base ``QuerySet``
 235containing all entries that contain a headline starting with "What". The second
 236is a subset of the first, with an additional criteria that excludes records
 237whose ``pub_date`` is greater than now. The third is a subset of the first,
 238with an additional criteria that selects only the records whose ``pub_date`` is
 239greater than now. The initial ``QuerySet`` (``q1``) is unaffected by the
 240refinement process.
 241
 242.. _querysets-are-lazy:
 243
 244QuerySets are lazy
 245~~~~~~~~~~~~~~~~~~
 246
 247``QuerySets`` are lazy -- the act of creating a ``QuerySet`` doesn't involve any
 248database activity. You can stack filters together all day long, and Django won't
 249actually run the query until the ``QuerySet`` is *evaluated*. Take a look at
 250this example::
 251
 252    >>> q = Entry.objects.filter(headline__startswith="What")
 253    >>> q = q.filter(pub_date__lte=datetime.now())
 254    >>> q = q.exclude(body_text__icontains="food")
 255    >>> print q
 256
 257Though this looks like three database hits, in fact it hits the database only
 258once, at the last line (``print q``). In general, the results of a ``QuerySet``
 259aren't fetched from the database until you "ask" for them. When you do, the
 260``QuerySet`` is *evaluated* by accessing the database. For more details on
 261exactly when evaluation takes place, see :ref:`when-querysets-are-evaluated`.
 262
 263
 264.. _retrieving-single-object-with-get:
 265
 266Retrieving a single object with get
 267-----------------------------------
 268
 269``.filter()`` will always give you a ``QuerySet``, even if only a single
 270object matches the query - in this case, it will be a ``QuerySet`` containing
 271a single element.
 272
 273If you know there is only one object that matches your query, you can use
 274the ``get()`` method on a `Manager` which returns the object directly::
 275
 276    >>> one_entry = Entry.objects.get(pk=1)
 277
 278You can use any query expression with ``get()``, just like with ``filter()`` -
 279again, see `Field lookups`_ below.
 280
 281Note that there is a difference between using ``.get()``, and using
 282``.filter()`` with a slice of ``[0]``. If there are no results that match the
 283query, ``.get()`` will raise a ``DoesNotExist`` exception. This exception is an
 284attribute of the model class that the query is being performed on - so in the
 285code above, if there is no ``Entry`` object with a primary key of 1, Django will
 286raise ``Entry.DoesNotExist``.
 287
 288Similarly, Django will complain if more than one item matches the ``get()``
 289query. In this case, it will raise ``MultipleObjectsReturned``, which again is
 290an attribute of the model class itself.
 291
 292
 293Other QuerySet methods
 294----------------------
 295
 296Most of the time you'll use ``all()``, ``get()``, ``filter()`` and ``exclude()``
 297when you need to look up objects from the database. However, that's far from all
 298there is; see the :ref:`QuerySet API Reference <queryset-api>` for a complete
 299list of all the various ``QuerySet`` methods.
 300
 301.. _limiting-querysets:
 302
 303Limiting QuerySets
 304------------------
 305
 306Use a subset of Python's array-slicing syntax to limit your ``QuerySet`` to a
 307certain number of results. This is the equivalent of SQL's ``LIMIT`` and
 308``OFFSET`` clauses.
 309
 310For example, this returns the first 5 objects (``LIMIT 5``)::
 311
 312    >>> Entry.objects.all()[:5]
 313
 314This returns the sixth through tenth objects (``OFFSET 5 LIMIT 5``)::
 315
 316    >>> Entry.objects.all()[5:10]
 317
 318Negative indexing (i.e. ``Entry.objects.all()[-1]``) is not supported.
 319
 320Generally, slicing a ``QuerySet`` returns a new ``QuerySet`` -- it doesn't
 321evaluate the query. An exception is if you use the "step" parameter of Python
 322slice syntax. For example, this would actually execute the query in order to
 323return a list of every *second* object of the first 10::
 324
 325    >>> Entry.objects.all()[:10:2]
 326
 327To retrieve a *single* object rather than a list
 328(e.g. ``SELECT foo FROM bar LIMIT 1``), use a simple index instead of a
 329slice. For example, this returns the first ``Entry`` in the database, after
 330ordering entries alphabetically by headline::
 331
 332    >>> Entry.objects.order_by('headline')[0]
 333
 334This is roughly equivalent to::
 335
 336    >>> Entry.objects.order_by('headline')[0:1].get()
 337
 338Note, however, that the first of these will raise ``IndexError`` while the
 339second will raise ``DoesNotExist`` if no objects match the given criteria. See
 340:meth:`~django.db.models.QuerySet.get` for more details.
 341
 342.. _field-lookups-intro:
 343
 344Field lookups
 345-------------
 346
 347Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're
 348specified as keyword arguments to the ``QuerySet`` methods ``filter()``,
 349``exclude()`` and ``get()``.
 350
 351Basic lookups keyword arguments take the form ``field__lookuptype=value``.
 352(That's a double-underscore). For example::
 353
 354    >>> Entry.objects.filter(pub_date__lte='2006-01-01')
 355
 356translates (roughly) into the following SQL::
 357
 358    SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';
 359
 360.. admonition:: How this is possible
 361
 362   Python has the ability to define functions that accept arbitrary name-value
 363   arguments whose names and values are evaluated at runtime. For more
 364   information, see `Keyword Arguments`_ in the official Python tutorial.
 365
 366   .. _`Keyword Arguments`: http://docs.python.org/tutorial/controlflow.html#keyword-arguments
 367
 368If you pass an invalid keyword argument, a lookup function will raise
 369``TypeError``.
 370
 371The database API supports about two dozen lookup types; a complete reference
 372can be found in the :ref:`field lookup reference <field-lookups>`. To give you a taste of what's available, here's some of the more common lookups
 373you'll probably use:
 374
 375    :lookup:`exact`
 376        An "exact" match. For example::
 377
 378            >>> Entry.objects.get(headline__exact="Man bites dog")
 379
 380        Would generate SQL along these lines:
 381
 382        .. code-block:: sql
 383
 384            SELECT ... WHERE headline = 'Man bites dog';
 385
 386        If you don't provide a lookup type -- that is, if your keyword argument
 387        doesn't contain a double underscore -- the lookup type is assumed to be
 388        ``exact``.
 389
 390        For example, the following two statements are equivalent::
 391
 392            >>> Blog.objects.get(id__exact=14)  # Explicit form
 393            >>> Blog.objects.get(id=14)         # __exact is implied
 394
 395        This is for convenience, because ``exact`` lookups are the common case.
 396
 397    :lookup:`iexact`
 398        A case-insensitive match. So, the query::
 399
 400            >>> Blog.objects.get(name__iexact="beatles blog")
 401
 402        Would match a ``Blog`` titled "Beatles Blog", "beatles blog", or even
 403        "BeAtlES blOG".
 404
 405    :lookup:`contains`
 406        Case-sensitive containment test. For example::
 407
 408            Entry.objects.get(headline__contains='Lennon')
 409
 410        Roughly translates to this SQL:
 411
 412        .. code-block:: sql
 413
 414            SELECT ... WHERE headline LIKE '%Lennon%';
 415
 416        Note this will match the headline ``'Today Lennon honored'`` but not
 417        ``'today lennon honored'``.
 418
 419        There's also a case-insensitive version, :lookup:`icontains`.
 420
 421    :lookup:`startswith`, :lookup:`endswith`
 422        Starts-with and ends-with search, respectively. There are also
 423        case-insensitive versions called :lookup:`istartswith` and
 424        :lookup:`iendswith`.
 425
 426Again, this only scratches the surface. A complete reference can be found in the
 427:ref:`field lookup reference <field-lookups>`.
 428
 429Lookups that span relationships
 430-------------------------------
 431
 432Django offers a powerful and intuitive way to "follow" relationships in
 433lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the
 434scenes. To span a relationship, just use the field name of related fields
 435across models, separated by double underscores, until you get to the field you
 436want.
 437
 438This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name``
 439is ``'Beatles Blog'``::
 440
 441    >>> Entry.objects.filter(blog__name__exact='Beatles Blog')
 442
 443This spanning can be as deep as you'd like.
 444
 445It works backwards, too. To refer to a "reverse" relationship, just use the
 446lowercase name of the model.
 447
 448This example retrieves all ``Blog`` objects which have at least one ``Entry``
 449whose ``headline`` contains ``'Lennon'``::
 450
 451    >>> Blog.objects.filter(entry__headline__contains='Lennon')
 452
 453If you are filtering across multiple relationships and one of the intermediate
 454models doesn't have a value that meets the filter condition, Django will treat
 455it as if there is an empty (all values are ``NULL``), but valid, object there.
 456All this means is that no error will be raised. For example, in this filter::
 457
 458    Blog.objects.filter(entry__authors__name='Lennon')
 459
 460(if there was a related ``Author`` model), if there was no ``author``
 461associated with an entry, it would be treated as if there was also no ``name``
 462attached, rather than raising an error because of the missing ``author``.
 463Usually this is exactly what you want to have happen. The only case where it
 464might be confusing is if you are using ``isnull``. Thus::
 465
 466    Blog.objects.filter(entry__authors__name__isnull=True)
 467
 468will return ``Blog`` objects that have an empty ``name`` on the ``author`` and
 469also those which have an empty ``author`` on the ``entry``. If you don't want
 470those latter objects, you could write::
 471
 472    Blog.objects.filter(entry__authors__isnull=False,
 473            entry__authors__name__isnull=True)
 474
 475Spanning multi-valued relationships
 476~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 477
 478When you are filtering an object based on a ``ManyToManyField`` or a reverse
 479``ForeignKey``, there are two different sorts of filter you may be
 480interested in. Consider the ``Blog``/``Entry`` relationship (``Blog`` to
 481``Entry`` is a one-to-many relation). We might be interested in finding blogs
 482that have an entry which has both *"Lennon"* in the headline and was published
 483in 2008. Or we might want to find blogs that have an entry with *"Lennon"* in
 484the headline as well as an entry that was published in 2008. Since there are
 485multiple entries associated with a single ``Blog``, both of these queries are
 486possible and make sense in some situations.
 487
 488The same type of situation arises with a ``ManyToManyField``. For example, if
 489an ``Entry`` has a ``ManyToManyField`` called ``tags``, we might want to find
 490entries linked to tags called *"music"* and *"bands"* or we might want an
 491entry that contains a tag with a name of *"music"* and a status of *"public"*.
 492
 493To handle both of these situations, Django has a consistent way of processing
 494``filter()`` and ``exclude()`` calls. Everything inside a single ``filter()``
 495call is applied simultaneously to filter out items matching all those
 496requirements. Successive ``filter()`` calls further restrict the set of
 497objects, but for multi-valued relations, they apply to any object linked to
 498the primary model, not necessarily those objects that were selected by an
 499earlier ``filter()`` call.
 500
 501That may sound a bit confusing, so hopefully an example will clarify. To
 502select all blogs that contain entries with both *"Lennon"* in the headline
 503and that were published in 2008 (the same entry satisfying both conditions),
 504we would write::
 505
 506    Blog.objects.filter(entry__headline__contains='Lennon',
 507            entry__pub_date__year=2008)
 508
 509To select all blogs that contain an entry with *"Lennon"* in the headline
 510**as well as** an entry that was published in 2008, we would write::
 511
 512    Blog.objects.filter(entry__headline__contains='Lennon').filter(
 513            entry__pub_date__year=2008)
 514
 515In this second example, the first filter restricted the queryset to all those
 516blogs linked to that particular type of entry. The second filter restricted
 517the set of blogs *further* to those that are also linked to the second type of
 518entry. The entries select by the second filter may or may not be the same as
 519the entries in the first filter. We are filtering the ``Blog`` items with each
 520filter statement, not the ``Entry`` items.
 521
 522All of this behavior also applies to ``exclude()``: all the conditions in a
 523single ``exclude()`` statement apply to a single instance (if those conditions
 524are talking about the same multi-valued relation). Conditions in subsequent
 525``filter()`` or ``exclude()`` calls that refer to the same relation may end up
 526filtering on different linked objects.
 527
 528.. _query-expressions:
 529
 530Filters can reference fields on the model
 531-----------------------------------------
 532
 533In the examples given so far, we have constructed filters that compare
 534the value of a model field with a constant. But what if you want to compare
 535the value of a model field with another field on the same model?
 536
 537Django provides the ``F()`` object to allow such comparisons. Instances
 538of ``F()`` act as a reference to a model field within a query. These
 539references can then be used in query filters to compare the values of two
 540different fields on the same model instance.
 541
 542For example, to find a list of all blog entries that have had more comments
 543than pingbacks, we construct an ``F()`` object to reference the pingback count,
 544and use that ``F()`` object in the query::
 545
 546    >>> from django.db.models import F
 547    >>> Entry.objects.filter(n_comments__gt=F('n_pingbacks'))
 548
 549Django supports the use of addition, subtraction, multiplication,
 550division and modulo arithmetic with ``F()`` objects, both with constants
 551and with other ``F()`` objects. To find all the blog entries with more than
 552*twice* as many comments as pingbacks, we modify the query::
 553
 554    >>> Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)
 555
 556To find all the entries where the rating of the entry is less than the
 557sum of the pingback count and comment count, we would issue the
 558query::
 559
 560    >>> Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks'))
 561
 562You can also use the double underscore notation to span relationships in
 563an ``F()`` object. An ``F()`` object with a double underscore will introduce
 564any joins needed to access the related object. For example, to retrieve all
 565the entries where the author's name is the same as the blog name, we could
 566issue the query::
 567
 568    >>> Entry.objects.filter(authors__name=F('blog__name'))
 569
 570.. versionadded:: 1.3
 571
 572For date and date/time fields, you can add or subtract a ``datetime.timedelta``
 573object.  The following would return all entries that were modified more than 3 days
 574after they were published::
 575   
 576    >>> from datetime import timedelta
 577    >>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))
 578
 579The pk lookup shortcut
 580----------------------
 581
 582For convenience, Django provides a ``pk`` lookup shortcut, which stands for
 583"primary key".
 584
 585In the example ``Blog`` model, the primary key is the ``id`` field, so these
 586three statements are equivalent::
 587
 588    >>> Blog.objects.get(id__exact=14) # Explicit form
 589    >>> Blog.objects.get(id=14) # __exact is implied
 590    >>> Blog.objects.get(pk=14) # pk implies id__exact
 591
 592The use of ``pk`` isn't limited to ``__exact`` queries -- any query term
 593can be combined with ``pk`` to perform a query on the primary key of a model::
 594
 595    # Get blogs entries with id 1, 4 and 7
 596    >>> Blog.objects.filter(pk__in=[1,4,7])
 597
 598    # Get all blog entries with id > 14
 599    >>> Blog.objects.filter(pk__gt=14)
 600
 601``pk`` lookups also work across joins. For example, these three statements are
 602equivalent::
 603
 604    >>> Entry.objects.filter(blog__id__exact=3) # Explicit form
 605    >>> Entry.objects.filter(blog__id=3)        # __exact is implied
 606    >>> Entry.objects.filter(blog__pk=3)        # __pk implies __id__exact
 607
 608Escaping percent signs and underscores in LIKE statements
 609---------------------------------------------------------
 610
 611The field lookups that equate to ``LIKE`` SQL statements (``iexact``,
 612``contains``, ``icontains``, ``startswith``, ``istartswith``, ``endswith``
 613and ``iendswith``) will automatically escape the two special characters used in
 614``LIKE`` statements -- the percent sign and the underscore. (In a ``LIKE``
 615statement, the percent sign signifies a multiple-character wildcard and the
 616underscore signifies a single-character wildcard.)
 617
 618This means things should work intuitively, so the abstraction doesn't leak.
 619For example, to retrieve all the entries that contain a percent sign, just use
 620the percent sign as any other character::
 621
 622    >>> Entry.objects.filter(headline__contains='%')
 623
 624Django takes care of the quoting for you; the resulting SQL will look something
 625like this:
 626
 627.. code-block:: sql
 628
 629    SELECT ... WHERE headline LIKE '%\%%';
 630
 631Same goes for underscores. Both percentage signs and underscores are handled
 632for you transparently.
 633
 634.. _caching-and-querysets:
 635
 636Caching and QuerySets
 637---------------------
 638
 639Each ``QuerySet`` contains a cache, to minimize database access. It's important
 640to understand how it works, in order to write the most efficient code.
 641
 642In a newly created ``QuerySet``, the cache is empty. The first time a
 643``QuerySet`` is evaluated -- and, hence, a database query happens -- Django
 644saves the query results in the ``QuerySet``'s cache and returns the results
 645that have been explicitly requested (e.g., the next element, if the
 646``QuerySet`` is being iterated over). Subsequent evaluations of the
 647``QuerySet`` reuse the cached results.
 648
 649Keep this caching behavior in mind, because it may bite you if you don't use
 650your ``QuerySet``\s correctly. For example, the following will create two
 651``QuerySet``\s, evaluate them, and throw them away::
 652
 653    >>> print [e.headline for e in Entry.objects.all()]
 654    >>> print [e.pub_date for e in Entry.objects.all()]
 655
 656That means the same database query will be executed twice, effectively doubling
 657your database load. Also, there's a possibility the two lists may not include
 658the same database records, because an ``Entry`` may have been added or deleted
 659in the split second between the two requests.
 660
 661To avoid this problem, simply save the ``QuerySet`` and reuse it::
 662
 663    >>> queryset = Entry.objects.all()
 664    >>> print [p.headline for p in queryset] # Evaluate the query set.
 665    >>> print [p.pub_date for p in queryset] # Re-use the cache from the evaluation.
 666
 667.. _complex-lookups-with-q:
 668
 669Complex lookups with Q objects
 670==============================
 671
 672Keyword argument queries -- in ``filter()``, etc. -- are "AND"ed together. If
 673you need to execute more complex queries (for example, queries with ``OR``
 674statements), you can use ``Q`` objects.
 675
 676A ``Q`` object (``django.db.models.Q``) is an object used to encapsulate a
 677collection of keyword arguments. These keyword arguments are specified as in
 678"Field lookups" above.
 679
 680For example, this ``Q`` object encapsulates a single ``LIKE`` query::
 681
 682    from django.db.models import Q
 683    Q(question__startswith='What')
 684
 685``Q`` objects can be combined using the ``&`` and ``|`` operators. When an
 686operator is used on two ``Q`` objects, it yields a new ``Q`` object.
 687
 688For example, this statement yields a single ``Q`` object that represents the
 689"OR" of two ``"question__startswith"`` queries::
 690
 691    Q(question__startswith='Who') | Q(question__startswith='What')
 692
 693This is equivalent to the following SQL ``WHERE`` clause::
 694
 695    WHERE question LIKE 'Who%' OR question LIKE 'What%'
 696
 697You can compose statements of arbitrary complexity by combining ``Q`` objects
 698with the ``&`` and ``|`` operators and use parenthetical grouping. Also, ``Q``
 699objects can be negated using the ``~`` operator, allowing for combined lookups
 700that combine both a normal query and a negated (``NOT``) query::
 701
 702    Q(question__startswith='Who') | ~Q(pub_date__year=2005)
 703
 704Each lookup function that takes keyword-arguments (e.g. ``filter()``,
 705``exclude()``, ``get()``) can also be passed one or more ``Q`` objects as
 706positional (not-named) arguments. If you provide multiple ``Q`` object
 707arguments to a lookup function, the arguments will be "AND"ed together. For
 708example::
 709
 710    Poll.objects.get(
 711        Q(question__startswith='Who'),
 712        Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
 713    )
 714
 715... roughly translates into the SQL::
 716
 717    SELECT * from polls WHERE question LIKE 'Who%'
 718        AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
 719
 720Lookup functions can mix the use of ``Q`` objects and keyword arguments. All
 721arguments provided to a lookup function (be they keyword arguments or ``Q``
 722objects) are "AND"ed together. However, if a ``Q`` object is provided, it must
 723precede the definition of any keyword arguments. For example::
 724
 725    Poll.objects.get(
 726        Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
 727        question__startswith='Who')
 728
 729... would be a valid query, equivalent to the previous example; but::
 730
 731    # INVALID QUERY
 732    Poll.objects.get(
 733        question__startswith='Who',
 734        Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))
 735
 736... would not be valid.
 737
 738.. seealso::
 739
 740    The `OR lookups examples`_ in the Django unit tests show some possible uses
 741    of ``Q``.
 742
 743    .. _OR lookups examples: http://code.djangoproject.com/browser/django/trunk/tests/modeltests/or_lookups/tests.py
 744
 745Comparing objects
 746=================
 747
 748To compare two model instances, just use the standard Python comparison operator,
 749the double equals sign: ``==``. Behind the scenes, that compares the primary
 750key values of two models.
 751
 752Using the ``Entry`` example above, the following two statements are equivalent::
 753
 754    >>> some_entry == other_entry
 755    >>> some_entry.id == other_entry.id
 756
 757If a model's primary key isn't called ``id``, no problem. Comparisons will
 758always use the primary key, whatever it's called. For example, if a model's
 759primary key field is called ``name``, these two statements are equivalent::
 760
 761    >>> some_obj == other_obj
 762    >>> some_obj.name == other_obj.name
 763
 764.. _topics-db-queries-delete:
 765
 766Deleting objects
 767================
 768
 769The delete method, conveniently, is named ``delete()``. This method immediately
 770deletes the object and has no return value. Example::
 771
 772    e.delete()
 773
 774You can also delete objects in bulk. Every ``QuerySet`` has a ``delete()``
 775method, which deletes all members of that ``QuerySet``.
 776
 777For example, this deletes all ``Entry`` objects with a ``pub_date`` year of
 7782005::
 779
 780    Entry.objects.filter(pub_date__year=2005).delete()
 781
 782Keep in mind that this will, whenever possible, be executed purely in
 783SQL, and so the ``delete()`` methods of individual object instances
 784will not necessarily be called during the process. If you've provided
 785a custom ``delete()`` method on a model class and want to ensure that
 786it is called, you will need to "manually" delete instances of that
 787model (e.g., by iterating over a ``QuerySet`` and calling ``delete()``
 788on each object individually) rather than using the bulk ``delete()``
 789method of a ``QuerySet``.
 790
 791When Django deletes an object, by default it emulates the behavior of the SQL
 792constraint ``ON DELETE CASCADE`` -- in other words, any objects which had
 793foreign keys pointing at the object to be deleted will be deleted along with
 794it. For example::
 795
 796    b = Blog.objects.get(pk=1)
 797    # This will delete the Blog and all of its Entry objects.
 798    b.delete()
 799
 800.. versionadded:: 1.3
 801    This cascade behavior is customizable via the
 802    :attr:`~django.db.models.ForeignKey.on_delete` argument to the
 803    :class:`~django.db.models.ForeignKey`.
 804
 805Note that ``delete()`` is the only ``QuerySet`` method that is not exposed on a
 806``Manager`` itself. This is a safety mechanism to prevent you from accidentally
 807requesting ``Entry.objects.delete()``, and deleting *all* the entries. If you
 808*do* want to delete all the objects, then you have to explicitly request a
 809complete query set::
 810
 811    Entry.objects.all().delete()
 812
 813.. _topics-db-queries-update:
 814
 815Updating multiple objects at once
 816=================================
 817
 818Sometimes you want to set a field to a particular value for all the objects in
 819a ``QuerySet``. You can do this with the ``update()`` method. For example::
 820
 821    # Update all the headlines with pub_date in 2007.
 822    Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')
 823
 824You can only set non-relation fields and ``ForeignKey`` fields using this
 825method. To update a non-relation field, provide the new value as a constant.
 826To update ``ForeignKey`` fields, set the new value to be the new model
 827instance you want to point to. For example::
 828
 829    >>> b = Blog.objects.get(pk=1)
 830
 831    # Change every Entry so that it belongs to this Blog.
 832    >>> Entry.objects.all().update(blog=b)
 833
 834The ``update()`` method is applied instantly and returns the number of rows
 835affected by the query. The only restriction on the ``QuerySet`` that is
 836updated is that it can only access one database table, the model's main
 837table. You can filter based on related fields, but you can only update columns
 838in the model's main table. Example::
 839
 840    >>> b = Blog.objects.get(pk=1)
 841
 842    # Update all the headlines belonging to this Blog.
 843    >>> Entry.objects.select_related().filter(blog=b).update(headline='Everything is the same')
 844
 845Be aware that the ``update()`` method is converted directly to an SQL
 846statement. It is a bulk operation for direct updates. It doesn't run any
 847``save()`` methods on your models, or emit the ``pre_save`` or ``post_save``
 848signals (which are a consequence of calling ``save()``). If you want to save
 849every item in a ``QuerySet`` and make sure that the ``save()`` method is
 850called on each instance, you don't need any special function to handle that.
 851Just loop over them and call ``save()``::
 852
 853    for item in my_queryset:
 854        item.save()
 855
 856Calls to update can also use :ref:`F() objects <query-expressions>` to update
 857one field based on the value of another field in the model. This is especially
 858useful for incrementing counters based upon their current value. For example, to
 859increment the pingback count for every entry in the blog::
 860
 861    >>> Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)
 862
 863However, unlike ``F()`` objects in filter and exclude clauses, you can't
 864introduce joins when you use ``F()`` objects in an update -- you can only
 865reference fields local to the model being updated. If you attempt to introduce
 866a join with an ``F()`` object, a ``FieldError`` will be raised::
 867
 868    # THIS WILL RAISE A FieldError
 869    >>> Entry.objects.update(headline=F('blog__name'))
 870
 871.. _topics-db-queries-related:
 872
 873Related objects
 874===============
 875
 876When you define a relationship in a model (i.e., a ``ForeignKey``,
 877``OneToOneField``, or ``ManyToManyField``), instances of that model will have
 878a convenient API to access the related object(s).
 879
 880Using the models at the top of this page, for example, an ``Entry`` object ``e``
 881can get its associated ``Blog`` object by accessing the ``blog`` attribute:
 882``e.blog``.
 883
 884(Behind the scenes, this functionality is implemented by Python descriptors_.
 885This shouldn't really matter to you, but we point it out here for the curious.)
 886
 887Django also creates API accessors for the "other" side of the relationship --
 888the link from the related model to the model that defines the relationship.
 889For example, a ``Blog`` object ``b`` has access to a list of all related
 890``Entry`` objects via the ``entry_set`` attribute: ``b.entry_set.all()``.
 891
 892All examples in this section use the sample ``Blog``, ``Author`` and ``Entry``
 893models defined at the top of this page.
 894
 895.. _descriptors: http://users.rcn.com/python/download/Descriptor.htm
 896
 897One-to-many relationships
 898-------------------------
 899
 900Forward
 901~~~~~~~
 902
 903If a model has a ``ForeignKey``, instances of that model will have access to
 904the related (foreign) object via a simple attribute of the model.
 905
 906Example::
 907
 908    >>> e = Entry.objects.get(id=2)
 909    >>> e.blog # Returns the related Blog object.
 910
 911You can get and set via a foreign-key attribute. As you may expect, changes to
 912the foreign key aren't saved to the database until you call ``save()``.
 913Example::
 914
 915    >>> e = Entry.objects.get(id=2)
 916    >>> e.blog = some_blog
 917    >>> e.save()
 918
 919If a ``ForeignKey`` field has ``null=True`` set (i.e., it allows ``NULL``
 920values), you can assign ``None`` to it. Example::
 921
 922    >>> e = Entry.objects.get(id=2)
 923    >>> e.blog = None
 924    >>> e.save() # "UPDATE blog_entry SET blog_id = NULL ...;"
 925
 926Forward access to one-to-many relationships is cached the first time the
 927related object is accessed. Subsequent accesses to the foreign key on the same
 928object instance are cached. Example::
 929
 930    >>> e = Entry.objects.get(id=2)
 931    >>> print e.blog  # Hits the database to retrieve the associated Blog.
 932    >>> print e.blog  # Doesn't hit the database; uses cached version.
 933
 934Note that the ``select_related()`` ``QuerySet`` method recursively prepopulates
 935the cache of all one-to-many relationships ahead of time. Example::
 936
 937    >>> e = Entry.objects.select_related().get(id=2)
 938    >>> print e.blog  # Doesn't hit the database; uses cached version.
 939    >>> print e.blog  # Doesn't hit the database; uses cached version.
 940
 941.. _backwards-related-objects:
 942
 943Following relationships "backward"
 944~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 945
 946If a model has a ``ForeignKey``, instances of the foreign-key model will have
 947access to a ``Manager`` that returns all instances of the first model. By
 948default, this ``Manager`` is named ``FOO_set``, where ``FOO`` is the source
 949model name, lowercased. This ``Manager`` returns ``QuerySets``, which can be
 950filtered and manipulated as described in the "Retrieving objects" section
 951above.
 952
 953Example::
 954
 955    >>> b = Blog.objects.get(id=1)
 956    >>> b.entry_set.all() # Returns all Entry objects related to Blog.
 957
 958    # b.entry_set is a Manager that returns QuerySets.
 959    >>> b.entry_set.filter(headline__contains='Lennon')
 960    >>> b.entry_set.count()
 961
 962You can override the ``FOO_set`` name by setting the ``related_name``
 963parameter in the ``ForeignKey()`` definition. For example, if the ``Entry``
 964model was altered to ``blog = ForeignKey(Blog, related_name='entries')``, the
 965above example code would look like this::
 966
 967    >>> b = Blog.objects.get(id=1)
 968    >>> b.entries.all() # Returns all Entry objects related to Blog.
 969
 970    # b.entries is a Manager that returns QuerySets.
 971    >>> b.entries.filter(headline__contains='Lennon')
 972    >>> b.entries.count()
 973
 974You cannot access a reverse ``ForeignKey`` ``Manager`` from the class; it must
 975be accessed from an instance::
 976
 977    >>> Blog.entry_set
 978    Traceback:
 979        ...
 980    AttributeError: "Manager must be accessed via instance".
 981
 982In addition to the ``QuerySet`` methods defined in "Retrieving objects" above,
 983the ``ForeignKey`` ``Manager`` has additional methods used to handle the set of
 984related objects. A synopsis of each is below, and complete details can be found
 985in the :doc:`related objects reference </ref/models/relations>`.
 986
 987``add(obj1, obj2, ...)``
 988    Adds the specified model objects to the related object set.
 989
 990``create(**kwargs)``
 991    Creates a new object, saves it and puts it in the related object set.
 992    Returns the newly created object.
 993
 994``remove(obj1, obj2, ...)``
 995    Removes the specified model objects from the related object set.
 996
 997``clear()``
 998    Removes all objects from the related object set.
 999
1000To assign the members of a related set in one fell swoop, just assign to it
1001from any iterable object. The iterable can contain object instances, or just
1002a list of primary key values. For example::
1003
1004    b = Blog.objects.get(id=1)
1005    b.entry_set = [e1, e2]
1006
1007In this example, ``e1`` and ``e2`` can be full Entry instances, or integer
1008primary key values.
1009
1010If the ``clear()`` method is available, any pre-existing objects will be
1011removed from the ``entry_set`` before all objects in the iterable (in this
1012case, a list) are added to the set. If the ``clear()`` method is *not*
1013available, all objects in the iterable will be added without removing any
1014existing elements.
1015
1016Each "reverse" operation described in this section has an immediate effect on
1017the database. Every addition, creation and deletion is immediately and
1018automatically saved to the database.
1019
1020Many-to-many relationships
1021--------------------------
1022
1023Both ends of a many-to-many relationship get automatic API access to the other
1024end. The API works just as a "backward" one-to-many relationship, above.
1025
1026The only difference is in the attribute naming: The model that defines the
1027``ManyToManyField`` uses the attribute name of that field itself, whereas the
1028"reverse" model uses the lowercased model name of the original model, plus
1029``'_set'`` (just like reverse one-to-many relationships).
1030
1031An example makes this easier to understand::
1032
1033    e = Entry.objects.get(id=3)
1034    e.authors.all() # Returns all Author objects for this Entry.
1035    e.authors.count()
1036    e.authors.filter(name__contains='John')
1037
1038    a = Author.objects.get(id=5)
1039    a.entry_set.all() # Returns all Entry objects for this Author.
1040
1041Like ``ForeignKey``, ``ManyToManyField`` can specify ``related_name``. In the
1042above example, if the ``ManyToManyField`` in ``Entry`` had specified
1043``related_name='entries'``, then each ``Author`` instance would have an
1044``entries`` attribute instead of ``entry_set``.
1045
1046One-to-one relationships
1047------------------------
1048
1049One-to-one relationships are very similar to many-to-one relationships. If you
1050define a :class:`~django.db.models.OneToOneField` on your model, instances of
1051that model will have access to the related object via a simple attribute of the
1052model.
1053
1054For example::
1055
1056    class EntryDetail(models.Model):
1057        entry = models.OneToOneField(Entry)
1058        details = models.TextField()
1059
1060    ed = EntryDetail.objects.get(id=2)
1061    ed.entry # Returns the related Entry object.
1062
1063The difference comes in "reverse" queries. The related model in a one-to-one
1064relationship also has access to a :class:`~django.db.models.Manager` object, but
1065that :class:`~django.db.models.Manager` represents a single object, rather than
1066a collection of objects::
1067
1068    e = Entry.objects.get(id=2)
1069    e.entrydetail # returns the related EntryDetail object
1070
1071If no object has been assigned to this relationship, Django will raise
1072a ``DoesNotExist`` exception.
1073
1074Instances can be assigned to the reverse relationship in the same way as
1075you would assign the forward relationship::
1076
1077    e.entrydetail = ed
1078
1079How are the backward relationships possible?
1080--------------------------------------------
1081
1082Other object-relational mappers require you to define relationships on both
1083sides. The Django developers believe this is a violation of the DRY (Don't
1084Repeat Yourself) principle, so Django only requires you to define the
1085relationship on one end.
1086
1087But how is this possible, given that a model class doesn't know which other
1088model classes are related to it until those other model classes are loaded?
1089
1090The answer lies in the :setting:`INSTALLED_APPS` setting. The first time any model is
1091loaded, Django iterates over every model in :setting:`INSTALLED_APPS` and creates the
1092backward relationships in memory as needed. Essentially, one of the functions
1093of :setting:`INSTALLED_APPS` is to tell Django the entire model domain.
1094
1095Queries over related objects
1096----------------------------
1097
1098Queries involving related objects follow the same rules as queries involving
1099normal value fields. When specifying the value for a query to match, you may
1100use either an object instance itself, or the primary key value for the object.
1101
1102For example, if you have a Blog object ``b`` with ``id=5``, the following
1103three queries would be identical::
1104
1105    Entry.objects.filter(blog=b) # Query using object instance
1106    Entry.objects.filter(blog=b.id) # Query using id from instance
1107    Entry.objects.filter(blog=5) # Query using id directly
1108
1109Falling back to raw SQL
1110=======================
1111
1112If you find yourself needing to write an SQL query that is too complex for
1113Django's database-mapper to handle, you can fall back on writing SQL by hand.
1114Django has a couple of options for writing raw SQL queries; see
1115:doc:`/topics/db/sql`.
1116
1117Finally, it's important to note that the Django database layer is merely an
1118interface to your database. You can access your database via other tools,
1119programming languages or database frameworks; there's nothing Django-specific
1120about your database.