PageRenderTime 44ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 0ms

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