| ====================== |
| Database API reference |
| ====================== |
| |
| Once you've created your `data models`_, Django automatically gives you a |
| database-abstraction API that lets you create, retrieve, update and delete |
| objects. This document explains that API. |
| |
| .. _`data models`: ../model_api/ |
| |
| Throughout this reference, we'll refer to the following models, which comprise |
| a weblog application:: |
| |
| class Blog(models.Model): |
| name = models.CharField(maxlength=100) |
| tagline = models.TextField() |
| |
| def __str__(self): |
| return self.name |
| |
| class Author(models.Model): |
| name = models.CharField(maxlength=50) |
| email = models.URLField() |
| |
| def __str__(self): |
| return self.name |
| |
| class Entry(models.Model): |
| blog = models.ForeignKey(Blog) |
| headline = models.CharField(maxlength=255) |
| body_text = models.TextField() |
| pub_date = models.DateTimeField() |
| authors = models.ManyToManyField(Author) |
| |
| def __str__(self): |
| return self.headline |
| |
| Creating objects |
| ================ |
| |
| To represent database-table data in Python objects, Django uses an intuitive |
| system: A model class represents a database table, and an instance of that |
| class represents a particular record in the database table. |
| |
| To create an object, instantiate it using keyword arguments to the model class, |
| then call ``save()`` to save it to the database. |
| |
| You import the model class from wherever it lives on the Python path, as you |
| may expect. (We point this out here because previous Django versions required |
| funky model importing.) |
| |
| Assuming models live in a file ``mysite/blog/models.py``, here's an example:: |
| |
| from mysite.blog.models import Blog |
| b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.') |
| b.save() |
| |
| This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit |
| the database until you explicitly call ``save()``. |
| |
| The ``save()`` method has no return value. |
| |
| To create an object and save it all in one step see the `create`__ method. |
| |
| __ `create(**kwargs)`_ |
| |
| Auto-incrementing primary keys |
| ------------------------------ |
| |
| If a model has an ``AutoField`` -- an auto-incrementing primary key -- then |
| that auto-incremented value will be calculated and saved as an attribute on |
| your object the first time you call ``save()``. |
| |
| Example:: |
| |
| b2 = Blog(name='Cheddar Talk', tagline='Thoughts on cheese.') |
| b2.id # Returns None, because b doesn't have an ID yet. |
| b2.save() |
| b2.id # Returns the ID of your new object. |
| |
| There's no way to tell what the value of an ID will be before you call |
| ``save()``, because that value is calculated by your database, not by Django. |
| |
| (For convenience, each model has an ``AutoField`` named ``id`` by default |
| unless you explicitly specify ``primary_key=True`` on a field. See the |
| `AutoField documentation`_.) |
| |
| .. _AutoField documentation: ../model_api/#autofield |
| |
| Explicitly specifying auto-primary-key values |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| If a model has an ``AutoField`` but you want to define a new object's ID |
| explicitly when saving, just define it explicitly before saving, rather than |
| relying on the auto-assignment of the ID. |
| |
| Example:: |
| |
| b3 = Blog(id=3, name='Cheddar Talk', tagline='Thoughts on cheese.') |
| b3.id # Returns 3. |
| b3.save() |
| b3.id # Returns 3. |
| |
| If you assign auto-primary-key values manually, make sure not to use an |
| already-existing primary-key value! If you create a new object with an explicit |
| primary-key value that already exists in the database, Django will assume |
| you're changing the existing record rather than creating a new one. |
| |
| Given the above ``'Cheddar Talk'`` blog example, this example would override |
| the previous record in the database:: |
| |
| b4 = Blog(id=3, name='Not Cheddar', tagline='Anything but cheese.') |
| b4.save() # Overrides the previous blog with ID=3! |
| |
| See _`How Django knows to UPDATE vs. INSERT`, below, for the reason this |
| happens. |
| |
| Explicitly specifying auto-primary-key values is mostly useful for bulk-saving |
| objects, when you're confident you won't have primary-key collision. |
| |
| Saving changes to objects |
| ========================= |
| |
| To save changes to an object that's already in the database, use ``save()``. |
| |
| Given a ``Blog`` instance ``b5`` that has already been saved to the database, |
| this example changes its name and updates its record in the database:: |
| |
| b5.name = 'New name' |
| b5.save() |
| |
| This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit |
| the database until you explicitly call ``save()``. |
| |
| The ``save()`` method has no return value. |
| |
| How Django knows to UPDATE vs. INSERT |
| ------------------------------------- |
| |
| You may have noticed Django database objects use the same ``save()`` method |
| for creating and changing objects. Django abstracts the need to use ``INSERT`` |
| or ``UPDATE`` SQL statements. Specifically, when you call ``save()``, Django |
| follows this algorithm: |
| |
| * If the object's primary key attribute is set to a value that evaluates to |
| ``True`` (i.e., a value other than ``None`` or the empty string), Django |
| executes a ``SELECT`` query to determine whether a record with the given |
| primary key already exists. |
| * If the record with the given primary key does already exist, Django |
| executes an ``UPDATE`` query. |
| * If the object's primary key attribute is *not* set, or if it's set but a |
| record doesn't exist, Django executes an ``INSERT``. |
| |
| The one gotcha here is that you should be careful not to specify a primary-key |
| value explicitly when saving new objects, if you cannot guarantee the |
| primary-key value is unused. For more on this nuance, see |
| "Explicitly specifying auto-primary-key values" above. |
| |
| Retrieving objects |
| ================== |
| |
| To retrieve objects from your database, you construct a ``QuerySet`` via a |
| ``Manager`` on your model class. |
| |
| A ``QuerySet`` represents a collection of objects from your database. It can |
| have zero, one or many *filters* -- criteria that narrow down the collection |
| based on given parameters. In SQL terms, a ``QuerySet`` equates to a ``SELECT`` |
| statement, and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``. |
| |
| You get a ``QuerySet`` by using your model's ``Manager``. Each model has at |
| least one ``Manager``, and it's called ``objects`` by default. Access it |
| directly via the model class, like so:: |
| |
| Blog.objects # <django.db.models.manager.Manager object at ...> |
| b = Blog(name='Foo', tagline='Bar') |
| b.objects # AttributeError: "Manager isn't accessible via Blog instances." |
| |
| (``Managers`` are accessible only via model classes, rather than from model |
| instances, to enforce a separation between "table-level" operations and |
| "record-level" operations.) |
| |
| The ``Manager`` is the main source of ``QuerySets`` for a model. It acts as a |
| "root" ``QuerySet`` that describes all objects in the model's database table. |
| For example, ``Blog.objects`` is the initial ``QuerySet`` that contains all |
| ``Blog`` objects in the database. |
| |
| Retrieving all objects |
| ---------------------- |
| |
| The simplest way to retrieve objects from a table is to get all of them. |
| To do this, use the ``all()`` method on a ``Manager``. |
| |
| Example:: |
| |
| all_entries = Entry.objects.all() |
| |
| The ``all()`` method returns a ``QuerySet`` of all the objects in the database. |
| |
| (If ``Entry.objects`` is a ``QuerySet``, why can't we just do ``Entry.objects``? |
| That's because ``Entry.objects``, the root ``QuerySet``, is a special case |
| that cannot be evaluated. The ``all()`` method returns a ``QuerySet`` that |
| *can* be evaluated.) |
| |
| Filtering objects |
| ----------------- |
| |
| The root ``QuerySet`` provided by the ``Manager`` describes all objects in the |
| database table. Usually, though, you'll need to select only a subset of the |
| complete set of objects. |
| |
| To create such a subset, you refine the initial ``QuerySet``, adding filter |
| conditions. The two most common ways to refine a ``QuerySet`` are: |
| |
| ``filter(**kwargs)`` |
| Returns a new ``QuerySet`` containing objects that match the given lookup |
| parameters. |
| |
| ``exclude(**kwargs)`` |
| Returns a new ``QuerySet`` containing objects that do *not* match the given |
| lookup parameters. |
| |
| The lookup parameters (``**kwargs`` in the above function definitions) should |
| be in the format described in `Field lookups`_ below. |
| |
| For example, to get a ``QuerySet`` of blog entries from the year 2006, use |
| ``filter()`` like so:: |
| |
| Entry.objects.filter(pub_date__year=2006) |
| |
| (Note we don't have to add an ``all()`` -- ``Entry.objects.all().filter(...)``. |
| That would still work, but you only need ``all()`` when you want all objects |
| from the root ``QuerySet``.) |
| |
| Chaining filters |
| ~~~~~~~~~~~~~~~~ |
| |
| The result of refining a ``QuerySet`` is itself a ``QuerySet``, so it's |
| possible to chain refinements together. For example:: |
| |
| Entry.objects.filter( |
| headline__startswith='What').exclude( |
| pub_date__gte=datetime.now()).filter( |
| pub_date__gte=datetime(2005, 1, 1)) |
| |
| ...takes the initial ``QuerySet`` of all entries in the database, adds a |
| filter, then an exclusion, then another filter. The final result is a |
| ``QuerySet`` containing all entries with a headline that starts with "What", |
| that were published between January 1, 2005, and the current day. |
| |
| Filtered QuerySets are unique |
| ----------------------------- |
| |
| Each time you refine a ``QuerySet``, you get a brand-new ``QuerySet`` that is |
| in no way bound to the previous ``QuerySet``. Each refinement creates a |
| separate and distinct ``QuerySet`` that can be stored, used and reused. |
| |
| Example:: |
| |
| q1 = Entry.objects.filter(headline__startswith="What") |
| q2 = q1.exclude(pub_date__gte=datetime.now()) |
| q3 = q1.filter(pub_date__gte=datetime.now()) |
| |
| These three ``QuerySets`` are separate. The first is a base ``QuerySet`` |
| containing all entries that contain a headline starting with "What". The second |
| is a subset of the first, with an additional criteria that excludes records |
| whose ``pub_date`` is greater than now. The third is a subset of the first, |
| with an additional criteria that selects only the records whose ``pub_date`` is |
| greater than now. The initial ``QuerySet`` (``q1``) is unaffected by the |
| refinement process. |
| |
| QuerySets are lazy |
| ------------------ |
| |
| ``QuerySets`` are lazy -- the act of creating a ``QuerySet`` doesn't involve |
| any database activity. You can stack filters together all day long, and Django |
| won't actually run the query until the ``QuerySet`` is *evaluated*. |
| |
| When QuerySets are evaluated |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| You can evaluate a ``QuerySet`` in the following ways: |
| |
| * **Iteration.** A ``QuerySet`` is iterable, and it executes its database |
| query the first time you iterate over it. For example, this will print |
| the headline of all entries in the database:: |
| |
| for e in Entry.objects.all(): |
| print e.headline |
| |
| * **Slicing.** As explained in `Limiting QuerySets`_ below, a ``QuerySet`` |
| can be sliced, using Python's array-slicing syntax. Usually slicing a |
| ``QuerySet`` returns another (unevaluated )``QuerySet``, but Django will |
| execute the database query if you use the "step" parameter of slice |
| syntax. |
| |
| * **repr().** A ``QuerySet`` is evaluated when you call ``repr()`` on it. |
| This is for convenience in the Python interactive interpreter, so you can |
| immediately see your results when using the API interactively. |
| |
| * **len().** A ``QuerySet`` is evaluated when you call ``len()`` on it. |
| This, as you might expect, returns the length of the result list. |
| |
| Note: *Don't* use ``len()`` on ``QuerySet``\s if all you want to do is |
| determine the number of records in the set. It's much more efficient to |
| handle a count at the database level, using SQL's ``SELECT COUNT(*)``, |
| and Django provides a ``count()`` method for precisely this reason. See |
| ``count()`` below. |
| |
| * **list().** Force evaluation of a ``QuerySet`` by calling ``list()`` on |
| it. For example:: |
| |
| entry_list = list(Entry.objects.all()) |
| |
| Be warned, though, that this could have a large memory overhead, because |
| Django will load each element of the list into memory. In contrast, |
| iterating over a ``QuerySet`` will take advantage of your database to |
| load data and instantiate objects only as you need them. |
| |
| Limiting QuerySets |
| ------------------ |
| |
| Use Python's array-slicing syntax to limit your ``QuerySet`` to a certain |
| number of results. This is the equivalent of SQL's ``LIMIT`` and ``OFFSET`` |
| clauses. |
| |
| For example, this returns the first 5 objects (``LIMIT 5``):: |
| |
| Entry.objects.all()[:5] |
| |
| This returns the fifth through tenth objects (``OFFSET 5 LIMIT 5``):: |
| |
| Entry.objects.all()[5:10] |
| |
| Generally, slicing a ``QuerySet`` returns a new ``QuerySet`` -- it doesn't |
| evaluate the query. An exception is if you use the "step" parameter of Python |
| slice syntax. For example, this would actually execute the query in order to |
| return a list of every *second* object of the first 10:: |
| |
| Entry.objects.all()[:10:2] |
| |
| To retrieve a *single* object rather than a list |
| (e.g. ``SELECT foo FROM bar LIMIT 1``), use a simple index instead of a |
| slice. For example, this returns the first ``Entry`` in the database, after |
| ordering entries alphabetically by headline:: |
| |
| Entry.objects.order_by('headline')[0] |
| |
| This is roughly equivalent to:: |
| |
| Entry.objects.order_by('headline')[0:1].get() |
| |
| Note, however, that the first of these will raise ``IndexError`` while the |
| second will raise ``DoesNotExist`` if no objects match the given criteria. |
| |
| QuerySet methods that return new QuerySets |
| ------------------------------------------ |
| |
| Django provides a range of ``QuerySet`` refinement methods that modify either |
| the types of results returned by the ``QuerySet`` or the way its SQL query is |
| executed. |
| |
| ``filter(**kwargs)`` |
| ~~~~~~~~~~~~~~~~~~~~ |
| |
| Returns a new ``QuerySet`` containing objects that match the given lookup |
| parameters. |
| |
| The lookup parameters (``**kwargs``) should be in the format described in |
| `Field lookups`_ below. Multiple parameters are joined via ``AND`` in the |
| underlying SQL statement. |
| |
| ``exclude(**kwargs)`` |
| ~~~~~~~~~~~~~~~~~~~~~ |
| |
| Returns a new ``QuerySet`` containing objects that do *not* match the given |
| lookup parameters. |
| |
| The lookup parameters (``**kwargs``) should be in the format described in |
| `Field lookups`_ below. Multiple parameters are joined via ``AND`` in the |
| underlying SQL statement, and the whole thing is enclosed in a ``NOT()``. |
| |
| This example excludes all entries whose ``pub_date`` is the current date/time |
| AND whose ``headline`` is "Hello":: |
| |
| Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello') |
| |
| In SQL terms, that evaluates to:: |
| |
| SELECT ... |
| WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello') |
| |
| This example excludes all entries whose ``pub_date`` is the current date/time |
| OR whose ``headline`` is "Hello":: |
| |
| Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3)).exclude(headline='Hello') |
| |
| In SQL terms, that evaluates to:: |
| |
| SELECT ... |
| WHERE NOT pub_date > '2005-1-3' |
| AND NOT headline = 'Hello' |
| |
| Note the second example is more restrictive. |
| |
| ``order_by(*fields)`` |
| ~~~~~~~~~~~~~~~~~~~~~ |
| |
| By default, results returned by a ``QuerySet`` are ordered by the ordering |
| tuple given by the ``ordering`` option in the model's ``Meta``. You can |
| override this on a per-``QuerySet`` basis by using the ``order_by`` method. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__year=2005).order_by('-pub_date', 'headline') |
| |
| The result above will be ordered by ``pub_date`` descending, then by |
| ``headline`` ascending. The negative sign in front of ``"-pub_date"`` indicates |
| *descending* order. Ascending order is implied. To order randomly, use ``"?"``, |
| like so:: |
| |
| Entry.objects.order_by('?') |
| |
| To order by a field in a different table, add the other table's name and a dot, |
| like so:: |
| |
| Entry.objects.order_by('blogs_blog.name', 'headline') |
| |
| There's no way to specify whether ordering should be case sensitive. With |
| respect to case-sensitivity, Django will order results however your database |
| backend normally orders them. |
| |
| ``distinct()`` |
| ~~~~~~~~~~~~~~ |
| |
| Returns a new ``QuerySet`` that uses ``SELECT DISTINCT`` in its SQL query. This |
| eliminates duplicate rows from the query results. |
| |
| By default, a ``QuerySet`` will not eliminate duplicate rows. In practice, this |
| is rarely a problem, because simple queries such as ``Blog.objects.all()`` |
| don't introduce the possibility of duplicate result rows. |
| |
| However, if your query spans multiple tables, it's possible to get duplicate |
| results when a ``QuerySet`` is evaluated. That's when you'd use ``distinct()``. |
| |
| ``values(*fields)`` |
| ~~~~~~~~~~~~~~~~~~~ |
| |
| Returns a ``ValuesQuerySet`` -- a ``QuerySet`` that evaluates to a list of |
| dictionaries instead of model-instance objects. |
| |
| Each of those dictionaries represents an object, with the keys corresponding to |
| the attribute names of model objects. |
| |
| This example compares the dictionaries of ``values()`` with the normal model |
| objects:: |
| |
| # This list contains a Blog object. |
| >>> Blog.objects.filter(name__startswith='Beatles') |
| [Beatles Blog] |
| |
| # This list contains a dictionary. |
| >>> Blog.objects.filter(name__startswith='Beatles').values() |
| [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}] |
| |
| ``values()`` takes optional positional arguments, ``*fields``, which specify |
| field names to which the ``SELECT`` should be limited. If you specify the |
| fields, each dictionary will contain only the field keys/values for the fields |
| you specify. If you don't specify the fields, each dictionary will contain a |
| key and value for every field in the database table. |
| |
| Example:: |
| |
| >>> Blog.objects.values() |
| [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}], |
| >>> Blog.objects.values('id', 'name') |
| [{'id': 1, 'name': 'Beatles Blog'}] |
| |
| A ``ValuesQuerySet`` is useful when you know you're only going to need values |
| from a small number of the available fields and you won't need the |
| functionality of a model instance object. It's more efficient to select only |
| the fields you need to use. |
| |
| Finally, note a ``ValuesQuerySet`` is a subclass of ``QuerySet``, so it has all |
| methods of ``QuerySet``. You can call ``filter()`` on it, or ``order_by()``, or |
| whatever. Yes, that means these two calls are identical:: |
| |
| Blog.objects.values().order_by('id') |
| Blog.objects.order_by('id').values() |
| |
| The people who made Django prefer to put all the SQL-affecting methods first, |
| followed (optionally) by any output-affecting methods (such as ``values()``), |
| but it doesn't really matter. This is your chance to really flaunt your |
| individualism. |
| |
| ``dates(field, kind, order='ASC')`` |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| Returns a ``DateQuerySet`` -- a ``QuerySet`` that evaluates to a list of |
| ``datetime.datetime`` objects representing all available dates of a particular |
| kind within the contents of the ``QuerySet``. |
| |
| ``field`` should be the name of a ``DateField`` or ``DateTimeField`` of your |
| model. |
| |
| ``kind`` should be either ``"year"``, ``"month"`` or ``"day"``. Each |
| ``datetime.datetime`` object in the result list is "truncated" to the given |
| ``type``. |
| |
| * ``"year"`` returns a list of all distinct year values for the field. |
| * ``"month"`` returns a list of all distinct year/month values for the field. |
| * ``"day"`` returns a list of all distinct year/month/day values for the field. |
| |
| ``order``, which defaults to ``'ASC'``, should be either ``'ASC'`` or |
| ``'DESC'``. This specifies how to order the results. |
| |
| Examples:: |
| |
| >>> Entry.objects.dates('pub_date', 'year') |
| [datetime.datetime(2005, 1, 1)] |
| >>> Entry.objects.dates('pub_date', 'month') |
| [datetime.datetime(2005, 2, 1), datetime.datetime(2005, 3, 1)] |
| >>> Entry.objects.dates('pub_date', 'day') |
| [datetime.datetime(2005, 2, 20), datetime.datetime(2005, 3, 20)] |
| >>> Entry.objects.dates('pub_date', 'day', order='DESC') |
| [datetime.datetime(2005, 3, 20), datetime.datetime(2005, 2, 20)] |
| >>> Entry.objects.filter(headline__contains='Lennon').dates('pub_date', 'day') |
| [datetime.datetime(2005, 3, 20)] |
| |
| ``none()`` |
| ~~~~~~~~~~ |
| |
| **New in Django development version** |
| |
| Returns an ``EmptyQuerySet`` -- a ``QuerySet`` that always evaluates to |
| an empty list. This can be used in cases where you know that you should |
| return an empty result set and your caller is expecting a ``QuerySet`` |
| object (instead of returning an empty list, for example.) |
| |
| Examples:: |
| |
| >>> Entry.objects.none() |
| [] |
| |
| ``select_related()`` |
| ~~~~~~~~~~~~~~~~~~~~ |
| |
| Returns a ``QuerySet`` that will automatically "follow" foreign-key |
| relationships, selecting that additional related-object data when it executes |
| its query. This is a performance booster which results in (sometimes much) |
| larger queries but means later use of foreign-key relationships won't require |
| database queries. |
| |
| The following examples illustrate the difference between plain lookups and |
| ``select_related()`` lookups. Here's standard lookup:: |
| |
| # Hits the database. |
| e = Entry.objects.get(id=5) |
| |
| # Hits the database again to get the related Blog object. |
| b = e.blog |
| |
| And here's ``select_related`` lookup:: |
| |
| # Hits the database. |
| e = Entry.objects.select_related().get(id=5) |
| |
| # Doesn't hit the database, because e.blog has been prepopulated |
| # in the previous query. |
| b = e.blog |
| |
| ``select_related()`` follows foreign keys as far as possible. If you have the |
| following models:: |
| |
| class City(models.Model): |
| # ... |
| |
| class Person(models.Model): |
| # ... |
| hometown = models.ForeignKey(City) |
| |
| class Book(models.Model): |
| # ... |
| author = models.ForeignKey(Person) |
| |
| ...then a call to ``Book.objects.select_related().get(id=4)`` will cache the |
| related ``Person`` *and* the related ``City``:: |
| |
| b = Book.objects.select_related().get(id=4) |
| p = b.author # Doesn't hit the database. |
| c = p.hometown # Doesn't hit the database. |
| |
| sv = Book.objects.get(id=4) # No select_related() in this example. |
| p = b.author # Hits the database. |
| c = p.hometown # Hits the database. |
| |
| Note that ``select_related()`` does not follow foreign keys that have |
| ``null=True``. |
| |
| Usually, using ``select_related()`` can vastly improve performance because your |
| app can avoid many database calls. However, in situations with deeply nested |
| sets of relationships ``select_related()`` can sometimes end up following "too |
| many" relations, and can generate queries so large that they end up being slow. |
| |
| In these situations, you can use the ``depth`` argument to ``select_related()`` |
| to control how many "levels" of relations ``select_related()`` will actually |
| follow:: |
| |
| b = Book.objects.select_related(depth=1).get(id=4) |
| p = b.author # Doesn't hit the database. |
| c = p.hometown # Requires a database call. |
| |
| The ``depth`` argument is new in the Django development version. |
| |
| ``extra(select=None, where=None, params=None, tables=None)`` |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| Sometimes, the Django query syntax by itself can't easily express a complex |
| ``WHERE`` clause. For these edge cases, Django provides the ``extra()`` |
| ``QuerySet`` modifier -- a hook for injecting specific clauses into the SQL |
| generated by a ``QuerySet``. |
| |
| By definition, these extra lookups may not be portable to different database |
| engines (because you're explicitly writing SQL code) and violate the DRY |
| principle, so you should avoid them if possible. |
| |
| Specify one or more of ``params``, ``select``, ``where`` or ``tables``. None |
| of the arguments is required, but you should use at least one of them. |
| |
| ``select`` |
| The ``select`` argument lets you put extra fields in the ``SELECT`` clause. |
| It should be a dictionary mapping attribute names to SQL clauses to use to |
| calculate that attribute. |
| |
| Example:: |
| |
| Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"}) |
| |
| As a result, each ``Entry`` object will have an extra attribute, |
| ``is_recent``, a boolean representing whether the entry's ``pub_date`` is |
| greater than Jan. 1, 2006. |
| |
| Django inserts the given SQL snippet directly into the ``SELECT`` |
| statement, so the resulting SQL of the above example would be:: |
| |
| SELECT blog_entry.*, (pub_date > '2006-01-01') |
| FROM blog_entry; |
| |
| |
| The next example is more advanced; it does a subquery to give each |
| resulting ``Blog`` object an ``entry_count`` attribute, an integer count |
| of associated ``Entry`` objects:: |
| |
| Blog.objects.extra( |
| select={ |
| 'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id' |
| }, |
| ) |
| |
| (In this particular case, we're exploiting the fact that the query will |
| already contain the ``blog_blog`` table in its ``FROM`` clause.) |
| |
| The resulting SQL of the above example would be:: |
| |
| SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) |
| FROM blog_blog; |
| |
| Note that the parenthesis required by most database engines around |
| subqueries are not required in Django's ``select`` clauses. Also note that |
| some database backends, such as some MySQL versions, don't support |
| subqueries. |
| |
| ``where`` / ``tables`` |
| You can define explicit SQL ``WHERE`` clauses -- perhaps to perform |
| non-explicit joins -- by using ``where``. You can manually add tables to |
| the SQL ``FROM`` clause by using ``tables``. |
| |
| ``where`` and ``tables`` both take a list of strings. All ``where`` |
| parameters are "AND"ed to any other search criteria. |
| |
| Example:: |
| |
| Entry.objects.extra(where=['id IN (3, 4, 5, 20)']) |
| |
| ...translates (roughly) into the following SQL:: |
| |
| SELECT * FROM blog_entry WHERE id IN (3, 4, 5, 20); |
| |
| ``params`` |
| The ``select`` and ``where`` parameters described above may use standard |
| Python database string placeholders -- ``'%s'`` to indicate parameters the |
| database engine should automatically quote. The ``params`` argument is a |
| list of any extra parameters to be substituted. |
| |
| Example:: |
| |
| Entry.objects.extra(where=['headline=%s'], params=['Lennon']) |
| |
| Always use ``params`` instead of embedding values directly into ``select`` |
| or ``where`` because ``params`` will ensure values are quoted correctly |
| according to your particular backend. (For example, quotes will be escaped |
| correctly.) |
| |
| Bad:: |
| |
| Entry.objects.extra(where=["headline='Lennon'"]) |
| |
| Good:: |
| |
| Entry.objects.extra(where=['headline=%s'], params=['Lennon']) |
| |
| QuerySet methods that do not return QuerySets |
| --------------------------------------------- |
| |
| The following ``QuerySet`` methods evaluate the ``QuerySet`` and return |
| something *other than* a ``QuerySet``. |
| |
| These methods do not use a cache (see _`Caching and QuerySets` below). Rather, |
| they query the database each time they're called. |
| |
| ``get(**kwargs)`` |
| ~~~~~~~~~~~~~~~~~ |
| |
| Returns the object matching the given lookup parameters, which should be in |
| the format described in `Field lookups`_. |
| |
| ``get()`` raises ``AssertionError`` if more than one object was found. |
| |
| ``get()`` raises a ``DoesNotExist`` exception if an object wasn't found for the |
| given parameters. The ``DoesNotExist`` exception is an attribute of the model |
| class. Example:: |
| |
| Entry.objects.get(id='foo') # raises Entry.DoesNotExist |
| |
| The ``DoesNotExist`` exception inherits from |
| ``django.core.exceptions.ObjectDoesNotExist``, so you can target multiple |
| ``DoesNotExist`` exceptions. Example:: |
| |
| from django.core.exceptions import ObjectDoesNotExist |
| try: |
| e = Entry.objects.get(id=3) |
| b = Blog.objects.get(id=1) |
| except ObjectDoesNotExist: |
| print "Either the entry or blog doesn't exist." |
| |
| ``create(**kwargs)`` |
| ~~~~~~~~~~~~~~~~~~~~ |
| |
| A convenience method for creating an object and saving it all in one step. Thus:: |
| |
| p = Person.objects.create(first_name="Bruce", last_name="Springsteen") |
| |
| and:: |
| |
| p = Person(first_name="Bruce", last_name="Springsteen") |
| p.save() |
| |
| are equivalent. |
| |
| ``get_or_create(**kwargs)`` |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| A convenience method for looking up an object with the given kwargs, creating |
| one if necessary. |
| |
| Returns a tuple of ``(object, created)``, where ``object`` is the retrieved or |
| created object and ``created`` is a boolean specifying whether a new object was |
| created. |
| |
| This is meant as a shortcut to boilerplatish code and is mostly useful for |
| data-import scripts. For example:: |
| |
| try: |
| obj = Person.objects.get(first_name='John', last_name='Lennon') |
| except Person.DoesNotExist: |
| obj = Person(first_name='John', last_name='Lennon', birthday=date(1940, 10, 9)) |
| obj.save() |
| |
| This pattern gets quite unwieldy as the number of fields in a model goes up. |
| The above example can be rewritten using ``get_or_create()`` like so:: |
| |
| obj, created = Person.objects.get_or_create(first_name='John', last_name='Lennon', |
| defaults={'birthday': date(1940, 10, 9)}) |
| |
| Any keyword arguments passed to ``get_or_create()`` -- *except* an optional one |
| called ``defaults`` -- will be used in a ``get()`` call. If an object is found, |
| ``get_or_create()`` returns a tuple of that object and ``False``. If an object |
| is *not* found, ``get_or_create()`` will instantiate and save a new object, |
| returning a tuple of the new object and ``True``. The new object will be |
| created according to this algorithm:: |
| |
| defaults = kwargs.pop('defaults', {}) |
| params = dict([(k, v) for k, v in kwargs.items() if '__' not in k]) |
| params.update(defaults) |
| obj = self.model(**params) |
| obj.save() |
| |
| In English, that means start with any non-``'defaults'`` keyword argument that |
| doesn't contain a double underscore (which would indicate a non-exact lookup). |
| Then add the contents of ``defaults``, overriding any keys if necessary, and |
| use the result as the keyword arguments to the model class. |
| |
| If you have a field named ``defaults`` and want to use it as an exact lookup in |
| ``get_or_create()``, just use ``'defaults__exact'``, like so:: |
| |
| Foo.objects.get_or_create(defaults__exact='bar', defaults={'defaults': 'baz'}) |
| |
| Finally, a word on using ``get_or_create()`` in Django views. As mentioned |
| earlier, ``get_or_create()`` is mostly useful in scripts that need to parse |
| data and create new records if existing ones aren't available. But if you need |
| to use ``get_or_create()`` in a view, please make sure to use it only in |
| ``POST`` requests unless you have a good reason not to. ``GET`` requests |
| shouldn't have any effect on data; use ``POST`` whenever a request to a page |
| has a side effect on your data. For more, see `Safe methods`_ in the HTTP spec. |
| |
| .. _Safe methods: http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html#sec9.1.1 |
| |
| ``count()`` |
| ~~~~~~~~~~~ |
| |
| Returns an integer representing the number of objects in the database matching |
| the ``QuerySet``. ``count()`` never raises exceptions. |
| |
| Example:: |
| |
| # Returns the total number of entries in the database. |
| Entry.objects.count() |
| |
| # Returns the number of entries whose headline contains 'Lennon' |
| Entry.objects.filter(headline__contains='Lennon').count() |
| |
| ``count()`` performs a ``SELECT COUNT(*)`` behind the scenes, so you should |
| always use ``count()`` rather than loading all of the record into Python |
| objects and calling ``len()`` on the result. |
| |
| Depending on which database you're using (e.g. PostgreSQL vs. MySQL), |
| ``count()`` may return a long integer instead of a normal Python integer. This |
| is an underlying implementation quirk that shouldn't pose any real-world |
| problems. |
| |
| ``in_bulk(id_list)`` |
| ~~~~~~~~~~~~~~~~~~~~ |
| |
| Takes a list of primary-key values and returns a dictionary mapping each |
| primary-key value to an instance of the object with the given ID. |
| |
| Example:: |
| |
| >>> Blog.objects.in_bulk([1]) |
| {1: Beatles Blog} |
| >>> Blog.objects.in_bulk([1, 2]) |
| {1: Beatles Blog, 2: Cheddar Talk} |
| >>> Blog.objects.in_bulk([]) |
| {} |
| |
| If you pass ``in_bulk()`` an empty list, you'll get an empty dictionary. |
| |
| ``latest(field_name=None)`` |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| Returns the latest object in the table, by date, using the ``field_name`` |
| provided as the date field. |
| |
| This example returns the latest ``Entry`` in the table, according to the |
| ``pub_date`` field:: |
| |
| Entry.objects.latest('pub_date') |
| |
| If your model's ``Meta`` specifies ``get_latest_by``, you can leave off the |
| ``field_name`` argument to ``latest()``. Django will use the field specified in |
| ``get_latest_by`` by default. |
| |
| Like ``get()``, ``latest()`` raises ``DoesNotExist`` if an object doesn't |
| exist with the given parameters. |
| |
| Note ``latest()`` exists purely for convenience and readability. |
| |
| Field lookups |
| ------------- |
| |
| Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're |
| specified as keyword arguments to the ``QuerySet`` methods ``filter()``, |
| ``exclude()`` and ``get()``. |
| |
| Basic lookups keyword arguments take the form ``field__lookuptype=value``. |
| (That's a double-underscore). For example:: |
| |
| Entry.objects.filter(pub_date__lte='2006-01-01') |
| |
| translates (roughly) into the following SQL:: |
| |
| SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01'; |
| |
| .. admonition:: How this is possible |
| |
| Python has the ability to define functions that accept arbitrary name-value |
| arguments whose names and values are evaluated at runtime. For more |
| information, see `Keyword Arguments`_ in the official Python tutorial. |
| |
| .. _`Keyword Arguments`: http://docs.python.org/tut/node6.html#SECTION006720000000000000000 |
| |
| If you pass an invalid keyword argument, a lookup function will raise |
| ``TypeError``. |
| |
| The database API supports the following lookup types: |
| |
| exact |
| ~~~~~ |
| |
| Exact match. If the value provided for comparison is ``None``, it will |
| be interpreted as an SQL ``NULL`` (See isnull_ for more details). |
| |
| Examples:: |
| |
| Entry.objects.get(id__exact=14) |
| Entry.objects.get(id__exact=None) |
| |
| SQL equivalents:: |
| |
| SELECT ... WHERE id = 14; |
| SELECT ... WHERE id = NULL; |
| |
| iexact |
| ~~~~~~ |
| |
| Case-insensitive exact match. |
| |
| Example:: |
| |
| Blog.objects.get(name__iexact='beatles blog') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE name ILIKE 'beatles blog'; |
| |
| Note this will match ``'Beatles Blog'``, ``'beatles blog'``, |
| ``'BeAtLes BLoG'``, etc. |
| |
| contains |
| ~~~~~~~~ |
| |
| Case-sensitive containment test. |
| |
| Example:: |
| |
| Entry.objects.get(headline__contains='Lennon') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline LIKE '%Lennon%'; |
| |
| Note this will match the headline ``'Today Lennon honored'`` but not |
| ``'today lennon honored'``. |
| |
| SQLite doesn't support case-sensitive ``LIKE`` statements; ``contains`` acts |
| like ``icontains`` for SQLite. |
| |
| icontains |
| ~~~~~~~~~ |
| |
| Case-insensitive containment test. |
| |
| Example:: |
| |
| Entry.objects.get(headline__icontains='Lennon') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline ILIKE '%Lennon%'; |
| |
| gt |
| ~~ |
| |
| Greater than. |
| |
| Example:: |
| |
| Entry.objects.filter(id__gt=4) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE id > 4; |
| |
| gte |
| ~~~ |
| |
| Greater than or equal to. |
| |
| lt |
| ~~ |
| |
| Less than. |
| |
| lte |
| ~~~ |
| |
| Less than or equal to. |
| |
| in |
| ~~ |
| |
| In a given list. |
| |
| Example:: |
| |
| Entry.objects.filter(id__in=[1, 3, 4]) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE id IN (1, 3, 4); |
| |
| startswith |
| ~~~~~~~~~~ |
| |
| Case-sensitive starts-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__startswith='Will') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline LIKE 'Will%'; |
| |
| SQLite doesn't support case-sensitive ``LIKE`` statements; ``startswith`` acts |
| like ``istartswith`` for SQLite. |
| |
| istartswith |
| ~~~~~~~~~~~ |
| |
| Case-insensitive starts-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__istartswith='will') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline ILIKE 'Will%'; |
| |
| endswith |
| ~~~~~~~~ |
| |
| Case-sensitive ends-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__endswith='cats') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline LIKE '%cats'; |
| |
| SQLite doesn't support case-sensitive ``LIKE`` statements; ``endswith`` acts |
| like ``iendswith`` for SQLite. |
| |
| iendswith |
| ~~~~~~~~~ |
| |
| Case-insensitive ends-with. |
| |
| Example:: |
| |
| Entry.objects.filter(headline__iendswith='will') |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE headline ILIKE '%will' |
| |
| range |
| ~~~~~ |
| |
| Range test (inclusive). |
| |
| Example:: |
| |
| start_date = datetime.date(2005, 1, 1) |
| end_date = datetime.date(2005, 3, 31) |
| Entry.objects.filter(pub_date__range=(start_date, end_date)) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE pub_date BETWEEN '2005-01-01' and '2005-03-31'; |
| |
| You can use ``range`` anywhere you can use ``BETWEEN`` in SQL -- for dates, |
| numbers and even characters. |
| |
| year |
| ~~~~ |
| |
| For date/datetime fields, exact year match. Takes a four-digit year. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__year=2005) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE EXTRACT('year' FROM pub_date) = '2005'; |
| |
| (The exact SQL syntax varies for each database engine.) |
| |
| month |
| ~~~~~ |
| |
| For date/datetime fields, exact month match. Takes an integer 1 (January) |
| through 12 (December). |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__month=12) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE EXTRACT('month' FROM pub_date) = '12'; |
| |
| (The exact SQL syntax varies for each database engine.) |
| |
| day |
| ~~~ |
| |
| For date/datetime fields, exact day match. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__day=3) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE EXTRACT('day' FROM pub_date) = '3'; |
| |
| (The exact SQL syntax varies for each database engine.) |
| |
| Note this will match any record with a pub_date on the third day of the month, |
| such as January 3, July 3, etc. |
| |
| isnull |
| ~~~~~~ |
| |
| Takes either ``True`` or ``False``, which correspond to SQL queries of |
| ``IS NULL`` and ``IS NOT NULL``, respectively. |
| |
| Example:: |
| |
| Entry.objects.filter(pub_date__isnull=True) |
| |
| SQL equivalent:: |
| |
| SELECT ... WHERE pub_date IS NULL; |
| |
| .. admonition:: ``__isnull=True`` vs ``__exact=None`` |
| |
| There is an important difference between ``__isnull=True`` and |
| ``__exact=None``. ``__exact=None`` will *always* return an empty result |
| set, because SQL requires that no value is equal to ``NULL``. |
| ``__isnull`` determines if the field is currently holding the value |
| of ``NULL`` without performing a comparison. |
| |
| search |
| ~~~~~~ |
| |
| A boolean full-text search, taking advantage of full-text indexing. This is |
| like ``contains`` but is significantly faster due to full-text indexing. |
| |
| Note this is only available in MySQL and requires direct manipulation of the |
| database to add the full-text index. |
| |
| Default lookups are exact |
| ------------------------- |
| |
| If you don't provide a lookup type -- that is, if your keyword argument doesn't |
| contain a double underscore -- the lookup type is assumed to be ``exact``. |
| |
| For example, the following two statements are equivalent:: |
| |
| Blog.objects.get(id__exact=14) # Explicit form |
| Blog.objects.get(id=14) # __exact is implied |
| |
| This is for convenience, because ``exact`` lookups are the common case. |
| |
| The pk lookup shortcut |
| ---------------------- |
| |
| For convenience, Django provides a ``pk`` lookup type, which stands for |
| "primary_key". |
| |
| In the example ``Blog`` model, the primary key is the ``id`` field, so these |
| three statements are equivalent:: |
| |
| Blog.objects.get(id__exact=14) # Explicit form |
| Blog.objects.get(id=14) # __exact is implied |
| Blog.objects.get(pk=14) # pk implies id__exact |
| |
| The use of ``pk`` isn't limited to ``__exact`` queries -- any query term |
| can be combined with ``pk`` to perform a query on the primary key of a model:: |
| |
| # Get blogs entries with id 1, 4 and 7 |
| Blog.objects.filter(pk__in=[1,4,7]) |
| # Get all blog entries with id > 14 |
| Blog.objects.filter(pk__gt=14) |
| |
| ``pk`` lookups also work across joins. For example, these three statements are |
| equivalent:: |
| |
| Entry.objects.filter(blog__id__exact=3) # Explicit form |
| Entry.objects.filter(blog__id=3) # __exact is implied |
| Entry.objects.filter(blog__pk=3) # __pk implies __id__exact |
| |
| Lookups that span relationships |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| Django offers a powerful and intuitive way to "follow" relationships in |
| lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the |
| scenes. To span a relationship, just use the field name of related fields |
| across models, separated by double underscores, until you get to the field you |
| want. |
| |
| This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name`` |
| is ``'Beatles Blog'``:: |
| |
| Entry.objects.filter(blog__name__exact='Beatles Blog') |
| |
| This spanning can be as deep as you'd like. |
| |
| It works backwards, too. To refer to a "reverse" relationship, just use the |
| lowercase name of the model. |
| |
| This example retrieves all ``Blog`` objects which have at least one ``Entry`` |
| whose ``headline`` contains ``'Lennon'``:: |
| |
| Blog.objects.filter(entry__headline__contains='Lennon') |
| |
| Escaping parenthesis and underscores in LIKE statements |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| The field lookups that equate to ``LIKE`` SQL statements (``iexact``, |
| ``contains``, ``icontains``, ``startswith``, ``istartswith``, ``endswith`` |
| and ``iendswith``) will automatically escape the two special characters used in |
| ``LIKE`` statements -- the percent sign and the underscore. (In a ``LIKE`` |
| statement, the percent sign signifies a multiple-character wildcard and the |
| underscore signifies a single-character wildcard.) |
| |
| This means things should work intuitively, so the abstraction doesn't leak. |
| For example, to retrieve all the entries that contain a percent sign, just use |
| the percent sign as any other character:: |
| |
| Entry.objects.filter(headline__contains='%') |
| |
| Django takes care of the quoting for you; the resulting SQL will look something |
| like this:: |
| |
| SELECT ... WHERE headline LIKE '%\%%'; |
| |
| Same goes for underscores. Both percentage signs and underscores are handled |
| for you transparently. |
| |
| Caching and QuerySets |
| --------------------- |
| |
| Each ``QuerySet`` contains a cache, to minimize database access. It's important |
| to understand how it works, in order to write the most efficient code. |
| |
| In a newly created ``QuerySet``, the cache is empty. The first time a |
| ``QuerySet`` is evaluated -- and, hence, a database query happens -- Django |
| saves the query results in the ``QuerySet``'s cache and returns the results |
| that have been explicitly requested (e.g., the next element, if the |
| ``QuerySet`` is being iterated over). Subsequent evaluations of the |
| ``QuerySet`` reuse the cached results. |
| |
| Keep this caching behavior in mind, because it may bite you if you don't use |
| your ``QuerySet``\s correctly. For example, the following will create two |
| ``QuerySet``\s, evaluate them, and throw them away:: |
| |
| print [e.headline for e in Entry.objects.all()] |
| print [e.pub_date for e in Entry.objects.all()] |
| |
| That means the same database query will be executed twice, effectively doubling |
| your database load. Also, there's a possibility the two lists may not include |
| the same database records, because an ``Entry`` may have been added or deleted |
| in the split second between the two requests. |
| |
| To avoid this problem, simply save the ``QuerySet`` and reuse it:: |
| |
| queryset = Poll.objects.all() |
| print [p.headline for p in queryset] # Evaluate the query set. |
| print [p.pub_date for p in queryset] # Re-use the cache from the evaluation. |
| |
| Comparing objects |
| ================= |
| |
| To compare two model instances, just use the standard Python comparison operator, |
| the double equals sign: ``==``. Behind the scenes, that compares the primary |
| key values of two models. |
| |
| Using the ``Entry`` example above, the following two statements are equivalent:: |
| |
| some_entry == other_entry |
| some_entry.id == other_entry.id |
| |
| If a model's primary key isn't called ``id``, no problem. Comparisons will |
| always use the primary key, whatever it's called. For example, if a model's |
| primary key field is called ``name``, these two statements are equivalent:: |
| |
| some_obj == other_obj |
| some_obj.name == other_obj.name |
| |
| Complex lookups with Q objects |
| ============================== |
| |
| Keyword argument queries -- in ``filter()``, etc. -- are "AND"ed together. If |
| you need to execute more complex queries (for example, queries with ``OR`` |
| statements), you can use ``Q`` objects. |
| |
| A ``Q`` object (``django.db.models.Q``) is an object used to encapsulate a |
| collection of keyword arguments. These keyword arguments are specified as in |
| "Field lookups" above. |
| |
| For example, this ``Q`` object encapsulates a single ``LIKE`` query:: |
| |
| Q(question__startswith='What') |
| |
| ``Q`` objects can be combined using the ``&`` and ``|`` operators. When an |
| operator is used on two ``Q`` objects, it yields a new ``Q`` object. |
| |
| For example, this statement yields a single ``Q`` object that represents the |
| "OR" of two ``"question__startswith"`` queries:: |
| |
| Q(question__startswith='Who') | Q(question__startswith='What') |
| |
| This is equivalent to the following SQL ``WHERE`` clause:: |
| |
| WHERE question LIKE 'Who%' OR question LIKE 'What%' |
| |
| You can compose statements of arbitrary complexity by combining ``Q`` objects |
| with the ``&`` and ``|`` operators. You can also use parenthetical grouping. |
| |
| Each lookup function that takes keyword-arguments (e.g. ``filter()``, |
| ``exclude()``, ``get()``) can also be passed one or more ``Q`` objects as |
| positional (not-named) arguments. If you provide multiple ``Q`` object |
| arguments to a lookup function, the arguments will be "AND"ed together. For |
| example:: |
| |
| Poll.objects.get( |
| Q(question__startswith='Who'), |
| Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)) |
| ) |
| |
| ... roughly translates into the SQL:: |
| |
| SELECT * from polls WHERE question LIKE 'Who%' |
| AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06') |
| |
| Lookup functions can mix the use of ``Q`` objects and keyword arguments. All |
| arguments provided to a lookup function (be they keyword arguments or ``Q`` |
| objects) are "AND"ed together. However, if a ``Q`` object is provided, it must |
| precede the definition of any keyword arguments. For example:: |
| |
| Poll.objects.get( |
| Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)), |
| question__startswith='Who') |
| |
| ... would be a valid query, equivalent to the previous example; but:: |
| |
| # INVALID QUERY |
| Poll.objects.get( |
| question__startswith='Who', |
| Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))) |
| |
| ... would not be valid. |
| |
| See the `OR lookups examples page`_ for more examples. |
| |
| .. _OR lookups examples page: http://www.djangoproject.com/documentation/models/or_lookups/ |
| |
| Related objects |
| =============== |
| |
| When you define a relationship in a model (i.e., a ``ForeignKey``, |
| ``OneToOneField``, or ``ManyToManyField``), instances of that model will have |
| a convenient API to access the related object(s). |
| |
| Using the models at the top of this page, for example, an ``Entry`` object ``e`` |
| can get its associated ``Blog`` object by accessing the ``blog`` attribute: |
| ``e.blog``. |
| |
| (Behind the scenes, this functionality is implemented by Python descriptors_. |
| This shouldn't really matter to you, but we point it out here for the curious.) |
| |
| Django also creates API accessors for the "other" side of the relationship -- |
| the link from the related model to the model that defines the relationship. |
| For example, a ``Blog`` object ``b`` has access to a list of all related |
| ``Entry`` objects via the ``entry_set`` attribute: ``b.entry_set.all()``. |
| |
| All examples in this section use the sample ``Blog``, ``Author`` and ``Entry`` |
| models defined at the top of this page. |
| |
| .. _descriptors: http://users.rcn.com/python/download/Descriptor.htm |
| |
| One-to-many relationships |
| ------------------------- |
| |
| Forward |
| ~~~~~~~ |
| |
| If a model has a ``ForeignKey``, instances of that model will have access to |
| the related (foreign) object via a simple attribute of the model. |
| |
| Example:: |
| |
| e = Entry.objects.get(id=2) |
| e.blog # Returns the related Blog object. |
| |
| You can get and set via a foreign-key attribute. As you may expect, changes to |
| the foreign key aren't saved to the database until you call ``save()``. |
| Example:: |
| |
| e = Entry.objects.get(id=2) |
| e.blog = some_blog |
| e.save() |
| |
| If a ``ForeignKey`` field has ``null=True`` set (i.e., it allows ``NULL`` |
| values), you can assign ``None`` to it. Example:: |
| |
| e = Entry.objects.get(id=2) |
| e.blog = None |
| e.save() # "UPDATE blog_entry SET blog_id = NULL ...;" |
| |
| Forward access to one-to-many relationships is cached the first time the |
| related object is accessed. Subsequent accesses to the foreign key on the same |
| object instance are cached. Example:: |
| |
| e = Entry.objects.get(id=2) |
| print e.blog # Hits the database to retrieve the associated Blog. |
| print e.blog # Doesn't hit the database; uses cached version. |
| |
| Note that the ``select_related()`` ``QuerySet`` method recursively prepopulates |
| the cache of all one-to-many relationships ahead of time. Example:: |
| |
| e = Entry.objects.select_related().get(id=2) |
| print e.blog # Doesn't hit the database; uses cached version. |
| print e.blog # Doesn't hit the database; uses cached version. |
| |
| ``select_related()`` is documented in the "QuerySet methods that return new |
| QuerySets" section above. |
| |
| Backward |
| ~~~~~~~~ |
| |
| If a model has a ``ForeignKey``, instances of the foreign-key model will have |
| access to a ``Manager`` that returns all instances of the first model. By |
| default, this ``Manager`` is named ``FOO_set``, where ``FOO`` is the source |
| model name, lowercased. This ``Manager`` returns ``QuerySets``, which can be |
| filtered and manipulated as described in the "Retrieving objects" section |
| above. |
| |
| Example:: |
| |
| b = Blog.objects.get(id=1) |
| b.entry_set.all() # Returns all Entry objects related to Blog. |
| |
| # b.entry_set is a Manager that returns QuerySets. |
| b.entry_set.filter(headline__contains='Lennon') |
| b.entry_set.count() |
| |
| You can override the ``FOO_set`` name by setting the ``related_name`` |
| parameter in the ``ForeignKey()`` definition. For example, if the ``Entry`` |
| model was altered to ``blog = ForeignKey(Blog, related_name='entries')``, the |
| above example code would look like this:: |
| |
| b = Blog.objects.get(id=1) |
| b.entries.all() # Returns all Entry objects related to Blog. |
| |
| # b.entries is a Manager that returns QuerySets. |
| b.entries.filter(headline__contains='Lennon') |
| b.entries.count() |
| |
| You cannot access a reverse ``ForeignKey`` ``Manager`` from the class; it must |
| be accessed from an instance. Example:: |
| |
| Blog.entry_set # Raises AttributeError: "Manager must be accessed via instance". |
| |
| In addition to the ``QuerySet`` methods defined in "Retrieving objects" above, |
| the ``ForeignKey`` ``Manager`` has these additional methods: |
| |
| * ``add(obj1, obj2, ...)``: Adds the specified model objects to the related |
| object set. |
| |
| Example:: |
| |
| b = Blog.objects.get(id=1) |
| e = Entry.objects.get(id=234) |
| b.entry_set.add(e) # Associates Entry e with Blog b. |
| |
| * ``create(**kwargs)``: Creates a new object, saves it and puts it in the |
| related object set. Returns the newly created object. |
| |
| Example:: |
| |
| b = Blog.objects.get(id=1) |
| e = b.entry_set.create(headline='Hello', body_text='Hi', pub_date=datetime.date(2005, 1, 1)) |
| # No need to call e.save() at this point -- it's already been saved. |
| |
| This is equivalent to (but much simpler than):: |
| |
| b = Blog.objects.get(id=1) |
| e = Entry(blog=b, headline='Hello', body_text='Hi', pub_date=datetime.date(2005, 1, 1)) |
| e.save() |
| |
| Note that there's no need to specify the keyword argument of the model |
| that defines the relationship. In the above example, we don't pass the |
| parameter ``blog`` to ``create()``. Django figures out that the new |
| ``Entry`` object's ``blog`` field should be set to ``b``. |
| |
| * ``remove(obj1, obj2, ...)``: Removes the specified model objects from the |
| related object set. |
| |
| Example:: |
| |
| b = Blog.objects.get(id=1) |
| e = Entry.objects.get(id=234) |
| b.entry_set.remove(e) # Disassociates Entry e from Blog b. |
| |
| In order to prevent database inconsistency, this method only exists on |
| ``ForeignKey`` objects where ``null=True``. If the related field can't be |
| set to ``None`` (``NULL``), then an object can't be removed from a |
| relation without being added to another. In the above example, removing |
| ``e`` from ``b.entry_set()`` is equivalent to doing ``e.blog = None``, |
| and because the ``blog`` ``ForeignKey`` doesn't have ``null=True``, this |
| is invalid. |
| |
| * ``clear()``: Removes all objects from the related object set. |
| |
| Example:: |
| |
| b = Blog.objects.get(id=1) |
| b.entry_set.clear() |
| |
| Note this doesn't delete the related objects -- it just disassociates |
| them. |
| |
| Just like ``remove()``, ``clear()`` is only available on ``ForeignKey``s |
| where ``null=True``. |
| |
| To assign the members of a related set in one fell swoop, just assign to it |
| from any iterable object. Example:: |
| |
| b = Blog.objects.get(id=1) |
| b.entry_set = [e1, e2] |
| |
| If the ``clear()`` method is available, any pre-existing objects will be |
| removed from the ``entry_set`` before all objects in the iterable (in this |
| case, a list) are added to the set. If the ``clear()`` method is *not* |
| available, all objects in the iterable will be added without removing any |
| existing elements. |
| |
| Each "reverse" operation described in this section has an immediate effect on |
| the database. Every addition, creation and deletion is immediately and |
| automatically saved to the database. |
| |
| Many-to-many relationships |
| -------------------------- |
| |
| Both ends of a many-to-many relationship get automatic API access to the other |
| end. The API works just as a "backward" one-to-many relationship. See Backward_ |
| above. |
| |
| The only difference is in the attribute naming: The model that defines the |
| ``ManyToManyField`` uses the attribute name of that field itself, whereas the |
| "reverse" model uses the lowercased model name of the original model, plus |
| ``'_set'`` (just like reverse one-to-many relationships). |
| |
| An example makes this easier to understand:: |
| |
| e = Entry.objects.get(id=3) |
| e.authors.all() # Returns all Author objects for this Entry. |
| e.authors.count() |
| e.authors.filter(name__contains='John') |
| |
| a = Author.objects.get(id=5) |
| a.entry_set.all() # Returns all Entry objects for this Author. |
| |
| Like ``ForeignKey``, ``ManyToManyField`` can specify ``related_name``. In the |
| above example, if the ``ManyToManyField`` in ``Entry`` had specified |
| ``related_name='entries'``, then each ``Author`` instance would have an |
| ``entries`` attribute instead of ``entry_set``. |
| |
| One-to-one relationships |
| ------------------------ |
| |
| The semantics of one-to-one relationships will be changing soon, so we don't |
| recommend you use them. |
| |
| How are the backward relationships possible? |
| -------------------------------------------- |
| |
| Other object-relational mappers require you to define relationships on both |
| sides. The Django developers believe this is a violation of the DRY (Don't |
| Repeat Yourself) principle, so Django only requires you to define the |
| relationship on one end. |
| |
| But how is this possible, given that a model class doesn't know which other |
| model classes are related to it until those other model classes are loaded? |
| |
| The answer lies in the ``INSTALLED_APPS`` setting. The first time any model is |
| loaded, Django iterates over every model in ``INSTALLED_APPS`` and creates the |
| backward relationships in memory as needed. Essentially, one of the functions |
| of ``INSTALLED_APPS`` is to tell Django the entire model domain. |
| |
| Queries over related objects |
| ---------------------------- |
| |
| Queries involving related objects follow the same rules as queries involving |
| normal value fields. When specifying the the value for a query to match, you |
| may use either an object instance itself, or the primary key value for the |
| object. |
| |
| For example, if you have a Blog object ``b`` with ``id=5``, the following |
| three queries would be identical:: |
| |
| Entry.objects.filter(blog=b) # Query using object instance |
| Entry.objects.filter(blog=b.id) # Query using id from instance |
| Entry.objects.filter(blog=5) # Query using id directly |
| |
| Deleting objects |
| ================ |
| |
| The delete method, conveniently, is named ``delete()``. This method immediately |
| deletes the object and has no return value. Example:: |
| |
| e.delete() |
| |
| You can also delete objects in bulk. Every ``QuerySet`` has a ``delete()`` |
| method, which deletes all members of that ``QuerySet``. |
| |
| For example, this deletes all ``Entry`` objects with a ``pub_date`` year of |
| 2005:: |
| |
| Entry.objects.filter(pub_date__year=2005).delete() |
| |
| When Django deletes an object, it emulates the behavior of the SQL |
| constraint ``ON DELETE CASCADE`` -- in other words, any objects which |
| had foreign keys pointing at the object to be deleted will be deleted |
| along with it. For example:: |
| |
| b = Blog.objects.get(pk=1) |
| # This will delete the Blog and all of its Entry objects. |
| b.delete() |
| |
| Note that ``delete()`` is the only ``QuerySet`` method that is not exposed on a |
| ``Manager`` itself. This is a safety mechanism to prevent you from accidentally |
| requesting ``Entry.objects.delete()``, and deleting *all* the entries. If you |
| *do* want to delete all the objects, then you have to explicitly request a |
| complete query set:: |
| |
| Entry.objects.all().delete() |
| |
| Extra instance methods |
| ====================== |
| |
| In addition to ``save()``, ``delete()``, a model object might get any or all |
| of the following methods: |
| |
| get_FOO_display() |
| ----------------- |
| |
| For every field that has ``choices`` set, the object will have a |
| ``get_FOO_display()`` method, where ``FOO`` is the name of the field. This |
| method returns the "human-readable" value of the field. For example, in the |
| following model:: |
| |
| GENDER_CHOICES = ( |
| ('M', 'Male'), |
| ('F', 'Female'), |
| ) |
| class Person(models.Model): |
| name = models.CharField(maxlength=20) |
| gender = models.CharField(maxlength=1, choices=GENDER_CHOICES) |
| |
| ...each ``Person`` instance will have a ``get_gender_display()`` method. Example:: |
| |
| >>> p = Person(name='John', gender='M') |
| >>> p.save() |
| >>> p.gender |
| 'M' |
| >>> p.get_gender_display() |
| 'Male' |
| |
| get_next_by_FOO(\**kwargs) and get_previous_by_FOO(\**kwargs) |
| ------------------------------------------------------------- |
| |
| For every ``DateField`` and ``DateTimeField`` that does not have ``null=True``, |
| the object will have ``get_next_by_FOO()`` and ``get_previous_by_FOO()`` |
| methods, where ``FOO`` is the name of the field. This returns the next and |
| previous object with respect to the date field, raising the appropriate |
| ``DoesNotExist`` exception when appropriate. |
| |
| Both methods accept optional keyword arguments, which should be in the format |
| described in `Field lookups`_ above. |
| |
| Note that in the case of identical date values, these methods will use the ID |
| as a fallback check. This guarantees that no records are skipped or duplicated. |
| For a full example, see the `lookup API sample model`_. |
| |
| .. _lookup API sample model: http://www.djangoproject.com/documentation/models/lookup/ |
| |
| get_FOO_filename() |
| ------------------ |
| |
| For every ``FileField``, the object will have a ``get_FOO_filename()`` method, |
| where ``FOO`` is the name of the field. This returns the full filesystem path |
| to the file, according to your ``MEDIA_ROOT`` setting. |
| |
| Note that ``ImageField`` is technically a subclass of ``FileField``, so every |
| model with an ``ImageField`` will also get this method. |
| |
| get_FOO_url() |
| ------------- |
| |
| For every ``FileField``, the object will have a ``get_FOO_url()`` method, |
| where ``FOO`` is the name of the field. This returns the full URL to the file, |
| according to your ``MEDIA_URL`` setting. If the value is blank, this method |
| returns an empty string. |
| |
| get_FOO_size() |
| -------------- |
| |
| For every ``FileField``, the object will have a ``get_FOO_size()`` method, |
| where ``FOO`` is the name of the field. This returns the size of the file, in |
| bytes. (Behind the scenes, it uses ``os.path.getsize``.) |
| |
| save_FOO_file(filename, raw_contents) |
| ------------------------------------- |
| |
| For every ``FileField``, the object will have a ``save_FOO_file()`` method, |
| where ``FOO`` is the name of the field. This saves the given file to the |
| filesystem, using the given filename. If a file with the given filename already |
| exists, Django adds an underscore to the end of the filename (but before the |
| extension) until the filename is available. |
| |
| get_FOO_height() and get_FOO_width() |
| ------------------------------------ |
| |
| For every ``ImageField``, the object will have ``get_FOO_height()`` and |
| ``get_FOO_width()`` methods, where ``FOO`` is the name of the field. This |
| returns the height (or width) of the image, as an integer, in pixels. |
| |
| Shortcuts |
| ========= |
| |
| As you develop views, you will discover a number of common idioms in the |
| way you use the database API. Django encodes some of these idioms as |
| shortcuts that can be used to simplify the process of writing views. |
| |
| get_object_or_404() |
| ------------------- |
| |
| One common idiom to use ``get()`` and raise ``Http404`` if the |
| object doesn't exist. This idiom is captured by ``get_object_or_404()``. |
| This function takes a Django model as its first argument and an |
| arbitrary number of keyword arguments, which it passes to the manager's |
| ``get()`` function. It raises ``Http404`` if the object doesn't |
| exist. For example:: |
| |
| # Get the Entry with a primary key of 3 |
| e = get_object_or_404(Entry, pk=3) |
| |
| When you provide a model to this shortcut function, the default manager |
| is used to execute the underlying ``get()`` query. If you don't want to |
| use the default manager, or you want to search a list of related objects, |
| you can provide ``get_object_or_404()`` with a manager object, instead. |
| For example:: |
| |
| # Get the author of blog instance `e` with a name of 'Fred' |
| a = get_object_or_404(e.authors, name='Fred') |
| |
| # Use a custom manager 'recent_entries' in the search for an |
| # entry with a primary key of 3 |
| e = get_object_or_404(Entry.recent_entries, pk=3) |
| |
| get_list_or_404() |
| ----------------- |
| |
| ``get_list_or_404`` behaves the same was as ``get_object_or_404()`` |
| -- except the it uses using ``filter()`` instead of ``get()``. It raises |
| ``Http404`` if the list is empty. |
| |
| Falling back to raw SQL |
| ======================= |
| |
| If you find yourself needing to write an SQL query that is too complex for |
| Django's database-mapper to handle, you can fall back into raw-SQL statement |
| mode. |
| |
| The preferred way to do this is by giving your model custom methods or custom |
| manager methods that execute queries. Although there's nothing in Django that |
| *requires* database queries to live in the model layer, this approach keeps all |
| your data-access logic in one place, which is smart from an code-organization |
| standpoint. For instructions, see `Executing custom SQL`_. |
| |
| Finally, it's important to note that the Django database layer is merely an |
| interface to your database. You can access your database via other tools, |
| programming languages or database frameworks; there's nothing Django-specific |
| about your database. |
| |
| .. _Executing custom SQL: ../model_api/#executing-custom-sql |