path: root/docs/db-api.txt

======================
Database API reference
======================

Once you've created your `data models`_, you'll need to lookup data from the
database.  This document explains the database abstraction API derived from the
models, and how to create, retrieve, and update objects.

.. _`data models`: http://www.djangoproject.com/documentation/model_api/

Throughout this reference, we'll refer to the following Poll application::

    class Poll(meta.Model):
        fields = (
            meta.SlugField('slug', unique_for_month='pub_date'),
            meta.CharField('question', maxlength=255),
            meta.DateTimeField('pub_date'),
            meta.DateTimeField('expire_date'),
        )

    class Choice(meta.Model):
        fields = (
            meta.ForeignKey(Poll, edit_inline=True, edit_inline_type=meta.TABULAR,
                num_in_admin=10, min_num_in_admin=5),
            meta.CharField('choice', maxlength=255, core=True),
            meta.IntegerField('votes', editable=False, default=0),
        )

Basic lookup functions
======================

Each model exposes three basic functions for lookups: ``get_object``,
``get_list``, and ``get_count``.  These functions all take the same arguments,
but ``get_object`` assumes that only a single record will be returned (and
raises ``AssertionError`` if that's not true), ``get_count`` simply returns a
count of objects matched by the lookup, and ``get_list`` returns a list of objects.

Field lookups
=============

Basic field lookups take the form ``field__lookuptype`` (that's a
double-underscore). For example::

    polls.get_list(pub_date__lte=datetime.datetime.now())

translates (roughly) into the following SQL::

    SELECT * FROM polls_polls WHERE pub_date < NOW();

.. 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 run time. For more
   information, see `Keyword Arguments`_ in the official Python tutorial.

The DB API supports the following lookup types:

    ===========  ==============================================================
    Type         Description
    ===========  ==============================================================
    exact        Exact match: ``polls.get_object(id__exact=14)``.
    iexact       Case-insensitive exact match:
                 ``polls.get_list(slug__iexact="foo")`` matches a slug of
                 ``foo``, ``FOO``, ``fOo``, etc.
    contains     Case-sensitive containment test:
                 ``polls.get_list(question__contains="spam")`` returns all polls
                 that contain "spam" in the question. (PostgreSQL only. MySQL
                 doesn't support case-sensitive LIKE statements; ``contains``
                 will act like ``icontains`` for MySQL.)
    icontains    Case-insensitive containment test.
    gt           Greater than: ``polls.get_list(id__gt=4)``.
    gte          Greater than or equal to.
    lt           Less than.
    lte          Less than or equal to.
    ne           Not equal to.
    in           In a given list: ``polls.get_list(id__in=[1, 3, 4])`` returns
                 a list of polls whose IDs are either 1, 3 or 4.
    startswith   Case-sensitive starts-with:
                 ``polls.get_list(question_startswith="Would")``. (PostgreSQL
                 only. MySQL doesn't support case-sensitive LIKE statements;
                 ``startswith`` will act like ``istartswith`` for MySQL.)
    endswith     Case-sensitive ends-with. (PostgreSQL only. MySQL doesn't
                 support case-sensitive LIKE statements; ``endswith`` will act
                 like ``iendswith`` for MySQL.)
    istartswith  Case-insensitive starts-with.
    iendswith    Case-insensitive ends-with.
    range        Range test:
                 ``polls.get_list(pub_date__range=(start_date, end_date))``
                 returns all polls with a pub_date between ``start_date``
                 and ``end_date`` (inclusive).
    year         For date/datetime fields, exact year match:
                 ``polls.get_count(pub_date__year=2005)``.
    month        For date/datetime fields, exact month match.
    day          For date/datetime fields, exact day match.
    isnull       True/False; does is IF NULL/IF NOT NULL lookup:
                 ``polls.get_list(expire_date__isnull=True)``.
    ===========  ==============================================================

Multiple lookups are allowed, of course, and are translated as "AND"s::

    polls.get_list(
        pub_date__year=2005,
        pub_date__month=1,
        question__startswith="Would",
    )

...retrieves all polls published in January 2005 that have a question starting with "Would."

For convenience, there's a ``pk`` lookup type, which translates into
``(primary_key)__exact``. In the polls example, these two statements are
equivalent::

    polls.get_object(id__exact=3)
    polls.get_object(pk=3)

``pk`` lookups also work across joins. In the polls example, these two
statements are equivalent::

    choices.get_list(poll__id__exact=3)
    choices.get_list(poll__pk=3)

.. _`Keyword Arguments`: http://docs.python.org/tut/node6.html#SECTION006720000000000000000

Ordering
========

The results are automatically ordered by the ordering tuple given by the
``ordering`` key in the model, but the ordering may be explicitly
provided by the ``order_by`` argument to a lookup::

    polls.get_list(
        pub_date__year=2005,
        pub_date__month=1,
        order_by=('-pub_date', 'question'),
    )

The result set above will be ordered by ``pub_date`` descending, then
by ``question`` ascending. The negative sign in front of "-pub_date" indicates
descending order. Ascending order is implied. To order randomly, use "?", like
so::

    polls.get_list(order_by=['?'])

Relationships (joins)
=====================

Joins may implicitly be performed by following relationships:
``choices.get_list(poll__slug__exact="eggs")`` fetches a list of ``Choice``
objects where the associated ``Poll`` has a slug of ``eggs``.  Multiple levels
of joins are allowed.

Given an instance of an object, related objects can be looked-up directly using
convenience functions. For example, if ``p`` is a ``Poll`` instance,
``p.get_choice_list()`` will return a list of all associated choices. Astute
readers will note that this is the same as
``choices.get_list(poll_id__exact=p.id)``, except clearer.

Each type of relationship creates a set of methods on each object in the
relationship. These methods are created in both directions, so objects that are
"related-to" need not explicitly define reverse relationships; that happens
automatically.

One-to-one relations
--------------------

Each object in a one-to-one relationship will have a ``get_relatedobject()``
method. For example::

    class Place(meta.Model):
        fields = (
            ...
        )

    class Restaurant(meta.Model):
        ...
        fields = (
            meta.OneToOneField(places.Place),
            ...
        )

In the above example, each ``Place`` will have a ``get_restaurant()`` method,
and each ``Restaurant`` will have a ``get_place()`` method.

Many-to-one relations
---------------------

In each many-to-one relationship, the related object will have a
``get_relatedobject()`` method, and the related-to object will have
``get_relatedobject()``, ``get_relatedobject_list()``, and
``get_relatedobject_count()`` methods (the same as the module-level
``get_object()``, ``get_list()``, and ``get_count()`` methods).

In the poll example above, here are the available choice methods on a ``Poll`` object ``p``::

    p.get_choice()
    p.get_choice_list()
    p.get_choice_count()

And a ``Choice`` object ``c`` has the following method::

    c.get_poll()

Many-to-many relations
----------------------

Many-to-many relations result in the same set of methods as `Many-to-one relations`_,
except that the ``get_relatedobject_list()`` function on the related object will
return a list of instances instead of a single instance.  So, if the relationship
between ``Poll`` and ``Choice`` was many-to-many, ``choice.get_poll_list()`` would
return a list.

Relationships across applications
---------------------------------

If a relation spans applications -- if ``Place`` was had a ManyToOne relation to
a ``geo.City`` object, for example -- the name of the other application will be
added to the method, i.e. ``place.get_geo_city()`` and
``city.get_places_place_list()``.

Selecting related objects
-------------------------

Relations are the bread and butter of databases, so there's an option to "follow"
all relationships and pre-fill them in a simple cache so that later calls to
objects with a one-to-many relationship don't have to hit the database. Do this by
passing ``select_related=True`` to a lookup. This results in (sometimes much) larger
queries, but it means that later use of relationships is much faster.

For example, using the Poll and Choice models from above, if you do the following::

    c = choices.get_object(id__exact=5, select_related=True)

Then subsequent calls to ``c.get_poll()`` won't hit the database.

Note that ``select_related`` follows foreign keys as far as possible. If you have the
following models::

    class Poll(meta.Model):
        ...

    class Choice(meta.Model):
        fields = (
            meta.ForeignKey(Poll),
            ...
        )

    class SingleVote(meta.Model):
        fields = (
            meta.ForeignKey(Choice),
            ...
        )

then a call to ``singlevotes.get_object(id__exact=4, select_related=True)`` will
cache the related choice *and* the related poll::

    >>> sv = singlevotes.get_object(id__exact=4, select_related=True)
    >>> c = sv.get_choice()        # Doesn't hit the database.
    >>> p = c.get_poll()           # Doesn't hit the database.

    >>> sv = singlevotes.get_object(id__exact=4) # Note no "select_related".
    >>> c = sv.get_choice()        # Hits the database.
    >>> p = c.get_poll()           # Hits the database.

Limiting selected rows
======================

The ``limit``, ``offset``, and ``distinct`` keywords can be used to control
which rows are returned.  Both ``limit`` and ``offset`` should be integers which
will be directly passed to the SQL ``LIMIT``/``OFFSET`` commands.

If ``distinct`` is True, only distinct rows will be returned. This is equivalent
to a ``SELECT DISTINCT`` SQL clause.

Other lookup options
====================

There are a few other ways of more directly controlling the generated SQL
for the lookup.  Note that by definition these extra lookups may not be
portable to different database engines (because you're explicitly writing
SQL code) and should be avoided if possible.:

``params``
----------

All the extra-SQL params described below may use standard Python string
formatting codes to indicate parameters that the database engine will
automatically quote.  The ``params`` argument can contain any extra
parameters to be substituted.

``select``
----------

The ``select`` keyword allows you to select extra fields.  This should be a
dictionary mapping attribute names to a SQL clause to use to calculate that
attribute. For example::

    polls.get_list(
        select={
            'choice_count': 'SELECT COUNT(*) FROM choices WHERE poll_id = polls.id'
        }
    )

Each of the resulting ``Poll`` objects will have an extra attribute, ``choice_count``,
an integer count of associated ``Choice`` objects. Note that the parenthesis required by
most database engines around sub-selects are not required in Django's ``select``
clauses.

``where`` / ``tables``
----------------------

If you need to explicitly pass extra ``WHERE`` clauses -- perhaps to perform
non-explicit joins -- use the ``where`` keyword. If you need to
join other tables into your query, you can pass their names to ``tables``.

``where`` and ``tables`` both take a list of strings. All ``where`` parameters
are "AND"ed to any other search criteria.

For example::

    polls.get_list(question__startswith='Who', where=['id IN (3, 4, 5, 20)'])

...translates (roughly) into the following SQL:

    SELECT * FROM polls_polls WHERE question LIKE 'Who%' AND id IN (3, 4, 5, 20);

Changing objects
================

Once you've retrieved an object from the database using any of the above
options, changing it is extremely easy.  Make changes directly to the
objects fields, then call the object's ``save()`` method::

    >>> p = polls.get_object(id__exact=15)
    >>> p.slug = "new_slug"
    >>> p.pub_date = datetime.datetime.now()
    >>> p.save()

Creating new objects
====================

Creating new objects (i.e. ``INSERT``) is done by creating new instances
of objects then calling save() on them::

    >>> p = polls.Poll(slug="eggs",
    ...                question="How do you like your eggs?",
    ...                pub_date=datetime.datetime.now(),
    ...                expire_date=some_future_date)
    >>> p.save()

Calling ``save()`` on an object with an id if ``None`` signifies to
Django that the object is new and should be inserted.

Related objects (i.e. ``Choices``) are created using convience functions::

    >>> p.add_choice(choice="Over easy", votes=0)
    >>> p.add_choice(choice="Scrambled", votes=0)
    >>> p.add_choice(choice="Fertilized", votes=0)
    >>> p.add_choice(choice="Poached", votes=0)
    >>> p.get_choice_count()
    4

Each of those ``add_choice`` methods is equivilent to (except obviously much
simpler than)::

    >>> c = polls.Choice(poll_id=p.id,
    ...                  choice="Over easy",
    ...                  votes=0)
    >>> c.save()

Note that when using the `add_foo()`` methods, you do not give any value
for the ``id`` field, nor do you give a value for the field that stores
the relation (``poll_id`` in this case).

Deleting objects
================

The delete method, conveniently, is named ``delete()``.