|
| 1 | +====================== |
| 2 | +Database API reference |
| 3 | +====================== |
| 4 | + |
| 5 | +XXX INTRO HERE XXX |
| 6 | + |
| 7 | +Throughout this reference, I'll be referring to the following Poll application:: |
| 8 | + |
| 9 | + class Poll(meta.Model): |
| 10 | + module_name = 'polls' |
| 11 | + verbose_name = 'poll' |
| 12 | + db_table = 'polls' |
| 13 | + fields = ( |
| 14 | + meta.SlugField('slug', 'slug', unique_for_month='pub_date'), |
| 15 | + meta.CharField('question', 'question', maxlength=255), |
| 16 | + meta.DateTimeField('pub_date', 'date published'), |
| 17 | + meta.DateTimeField('expire_date', 'expiration date'), |
| 18 | + ) |
| 19 | + |
| 20 | + class Choice(meta.Model): |
| 21 | + module_name = 'choices' |
| 22 | + verbose_name = 'choice' |
| 23 | + db_table = 'poll_choices' |
| 24 | + fields = ( |
| 25 | + meta.IntegerField('poll_id', 'poll', rel=meta.ManyToOne(Poll, 'poll', 'id', |
| 26 | + edit_inline=True, edit_inline_type=meta.TABULAR, num_in_admin=10, |
| 27 | + min_num_in_admin=5)), |
| 28 | + meta.CharField('choice', 'choice', maxlength=255, core=True), |
| 29 | + meta.IntegerField('votes', 'votes', editable=False, default=0), |
| 30 | + ) |
| 31 | + |
| 32 | +Basic lookup functions |
| 33 | +====================== |
| 34 | + |
| 35 | +Each model exposes three basic functions for lookups: ``get_object``, |
| 36 | +``get_list``, and ``get_count``. These functions all take the same arguments, |
| 37 | +but ``get_object`` assumes that only a single record will be returned (and |
| 38 | +raises an exception if that's not true), ``get_count`` simple returns a count of |
| 39 | +objects matched by the lookup, and ``get_list`` returns the entire list. |
| 40 | + |
| 41 | +Field lookups |
| 42 | +============= |
| 43 | + |
| 44 | +Basic field lookups take the form ``field__lookuptype`` (that's a |
| 45 | +double-underscore). For example:: |
| 46 | + |
| 47 | + polls.get_list(pub_date__lte=datetime.datetime.now()) |
| 48 | + |
| 49 | +translates (roughly) into the following SQL: |
| 50 | + |
| 51 | + SELECT * FROM polls WHERE pub_date < NOW(); |
| 52 | + |
| 53 | +The DB API supports the following lookup types: |
| 54 | + |
| 55 | + ========== ============================================================== |
| 56 | + Type Description |
| 57 | + ========== ============================================================== |
| 58 | + exact Exact match: ``polls.get_object(id__exact=14)`` |
| 59 | + iexact Case-insensitive exact match: |
| 60 | + ``polls.get_list(slug__iexact="foo")`` matches a slug of ``foo``, |
| 61 | + ``FOO``, ``fOo``, etc. |
| 62 | + contains Case-sensitive contains test: |
| 63 | + ``polls.get_list(question__contains="spam")`` returns all polls |
| 64 | + that contain "spam" in the question. |
| 65 | + icontains Case-insensitive contains |
| 66 | + gt Greater than: ``polls.get_list(id__gt=4)`` |
| 67 | + gte Greater than or equal to |
| 68 | + lt Less than |
| 69 | + lte Less than or equal to |
| 70 | + startswith Case-sensitive starts-with: |
| 71 | + ``polls.get_list(question_startswith="Would")`` |
| 72 | + endswith Case-sensitive ends-with |
| 73 | + range Range test: |
| 74 | + ``polls.get_list(pub_date__range=(start_date, end_date)`` |
| 75 | + returns all polls with a pub_date between ``start_date`` |
| 76 | + and ``end_date`` (inclusive). |
| 77 | + year For date/datetime fields, exact year match: |
| 78 | + ``polls.get_count(pub_date__year=2005)``. |
| 79 | + month For date/datetime fields, exact month match. |
| 80 | + day For date/datetime fields, exact day match. |
| 81 | + isnull True/False; does is IF NULL/IF NOT NULL lookup: |
| 82 | + ``polls.get_list(expire_date__isnull=True)``. |
| 83 | + ========== ============================================================== |
| 84 | + |
| 85 | +Multiple lookups are of course allowed, and are translated as "ands":: |
| 86 | + |
| 87 | + polls.get_list( |
| 88 | + pub_date__year=2005, |
| 89 | + pub_date__month=1, |
| 90 | + question__startswith="Would", |
| 91 | + ) |
| 92 | + |
| 93 | +retrieves all polls published in Jan. 2005 whose question starts with "Would." |
| 94 | + |
| 95 | +"Or" lookups are also possible:: |
| 96 | + |
| 97 | + XXX FIXME XXX |
| 98 | + |
| 99 | +Ordering |
| 100 | +======== |
| 101 | + |
| 102 | +The results are automatically ordered by the ordering tuple given by the |
| 103 | +``ordering`` key in the model, but the ordering may be explicitly |
| 104 | +provided by the ``order_by`` argument to a lookup:: |
| 105 | + |
| 106 | + polls.get_list( |
| 107 | + pub_date__year=2005, |
| 108 | + pub_date__month=1, |
| 109 | + order_by=(("pub_date", "DESC"), ("question", "ASC")), |
| 110 | + ) |
| 111 | + |
| 112 | +The result set above will be ordered by ``pub_date`` (descending), then |
| 113 | +by ``question`` (ascending). Just like in models, the ``order_by`` clause |
| 114 | +is a list of ordering tuples where the first element is the field and the |
| 115 | +second is "ASC" or "DESC" to order ascending or descending. You may also |
| 116 | +use the tuple ``(None, "RANDOM")`` to order the result set randomly. |
| 117 | + |
| 118 | +Relationships (joins) |
| 119 | +===================== |
| 120 | + |
| 121 | +Joins may implicitly be performed by following relationships: |
| 122 | +``choices.get_list(poll__slug__exact="eggs")`` fetches a list of ``Choice`` |
| 123 | +objects where the associated ``Poll`` has a slug of ``eggs``. Multiple levels |
| 124 | +of joins are allowed. |
| 125 | + |
| 126 | +Given an instance of an object, related objects can be looked up directly using |
| 127 | +connivence functions, for example, if ``poll`` is a ``Poll`` instance, |
| 128 | +``poll.get_choice_list()`` will return a list of all associated choices (astute |
| 129 | +readers will note that this is the same as |
| 130 | +``choices.get_list(poll_id__exact=poll.id)``, except clearer). |
| 131 | + |
| 132 | +Each type of relationship creates a set of methods on each object in the |
| 133 | +relationship. These created methods go both ways, so objects that are |
| 134 | +"related-to" need not explicitly define reverse relationships; that happens |
| 135 | +automatically. |
| 136 | + |
| 137 | +One-to-one relations |
| 138 | +-------------------- |
| 139 | + |
| 140 | +Each object in a one-to-one relationship will have a ``get_relatedobject()`` |
| 141 | +method. For example:: |
| 142 | + |
| 143 | + class Place(meta.Model): |
| 144 | + ... |
| 145 | + fields = ( |
| 146 | + ... |
| 147 | + ) |
| 148 | + |
| 149 | + class Restaurant(meta.Model): |
| 150 | + ... |
| 151 | + fields = ( |
| 152 | + meta.IntegerField('id', 'ID', primary_key=True, |
| 153 | + rel=meta.OneToOne(places.Place, 'place', 'id')), |
| 154 | + ... |
| 155 | + ) |
| 156 | + |
| 157 | +In the above example, each ``Place`` will have a ``get_restaurant()`` method, |
| 158 | +and each ``Restaurant`` will have a ``get_place()`` method. |
| 159 | + |
| 160 | +Many-to-one relations |
| 161 | +--------------------- |
| 162 | + |
| 163 | +In each many-to-one relationship the related object will have a |
| 164 | +``get_relatedobject()`` method, and the related-to object will have |
| 165 | +``get_relatedobject()``, ``get_relatedobject_list()``, and |
| 166 | +``get_relatedobject_count()`` methods (the same as the module-level |
| 167 | +``get_object()``, ``get_list()``, and ``get_count()`` methods). |
| 168 | + |
| 169 | +Thus, for the ``Poll`` example at the top, ``Choice`` objects will have a |
| 170 | +``get_poll()`` method, and ``Poll`` objects will have ``get_choice()``, |
| 171 | +``get_choice_list()``, and ``get_choice_count()`` functions. |
| 172 | + |
| 173 | +Many-to-many relations |
| 174 | +---------------------- |
| 175 | + |
| 176 | +Many-to-many relations result in the same set of methods as `Many-to-one relations`_, |
| 177 | +except that the ``get_relatedobjects()`` function on the related object will |
| 178 | +return a list of instances instead of a single instance. So, if the relationship |
| 179 | +between ``Poll`` and ``Choice`` was many-to-many, ``choice.get_polls()`` would |
| 180 | +return a list. |
| 181 | + |
| 182 | +Relationships across applications |
| 183 | +--------------------------------- |
| 184 | + |
| 185 | +If a relation spans applications -- if ``Place`` was had a ManyToOne relation to |
| 186 | +a ``geo.City`` object, for example -- the name of the other application will be |
| 187 | +added to the method, i.e. ``place.get_geo_city()`` and |
| 188 | +``city.get_places_place_list()``. |
| 189 | + |
| 190 | +Selecting related objects |
| 191 | +------------------------- |
| 192 | + |
| 193 | +Relations are the bread and butter of databases, so there's an option to "follow" |
| 194 | +all relationships and pre-fill them in a simple cache so that later calls to |
| 195 | +objects with a one-to-many relationship don't have to hit the database. If you pass |
| 196 | +``select_related=True`` to a lookup, this pre-caching of relationships will be performed. |
| 197 | +This results in (sometimes much) larger queries, but it means that later use of |
| 198 | +relationships is much faster. |
| 199 | + |
| 200 | +For example, using the Poll and Choice models from above, if you do the following:: |
| 201 | + |
| 202 | + c = choices.get_object(id__exact=5, select_related=True) |
| 203 | + |
| 204 | +Then subsequent calls to ``c.get_poll()`` won't hit the database. |
| 205 | + |
| 206 | +Limiting selected rows |
| 207 | +====================== |
| 208 | + |
| 209 | +The ``limit``, ``offset``, and ``distinct`` keywords can be used to control |
| 210 | +which rows are returned. Both ``limit`` and ``offset`` should be integers which |
| 211 | +will be directly passed to the SQL ``LIMIT``/``OFFSET`` commands. |
| 212 | + |
| 213 | +If ``distinct`` is True, only distinct rows will be returned (this is equivalent |
| 214 | +to a ``SELECT DISTINCT`` SQL clause). |
| 215 | + |
| 216 | +Other lookup options |
| 217 | +==================== |
| 218 | + |
| 219 | +There are a few other ways of more directly controlling the generated SQL |
| 220 | +for the lookup. Note that by definition these extra lookups may not be |
| 221 | +portable to different database engines (since you're explicitly writing |
| 222 | +SQL code) and should be avoided where ever possible.: |
| 223 | + |
| 224 | +``params`` |
| 225 | +---------- |
| 226 | + |
| 227 | +All the extra-SQL params described below may use standard Python string |
| 228 | +formatting codes to indicate parameters that the database engine will |
| 229 | +automatically quote. The ``params`` argument can contain any extra |
| 230 | +parameters to be substituted. |
| 231 | + |
| 232 | +``select`` |
| 233 | +---------- |
| 234 | + |
| 235 | +The ``select`` keyword allows you to select extra fields. This should be a |
| 236 | +dict mapping field names to a SQL clause to use for that field. For example:: |
| 237 | + |
| 238 | + polls.get_list( |
| 239 | + select={ |
| 240 | + 'choice_count' : 'SELECT COUNT(*) FROM choices WHERE poll_id = polls.id' |
| 241 | + } |
| 242 | + ) |
| 243 | + |
| 244 | +Each of the resulting ``Poll`` objects will have an extra ``choice_count`` with |
| 245 | +a count of associated ``Choice`` objects. Note that the parenthesis required by |
| 246 | +most database engines around sub-selects are not required in Django's ``select`` |
| 247 | +clauses. |
| 248 | + |
| 249 | +``where`` / ``tables`` |
| 250 | +---------------------- |
| 251 | + |
| 252 | +If you need to explicitly pass extra ``WHERE`` clauses -- perhaps to perform |
| 253 | +non-explicit joins -- use the ``where`` keyword.. If you need to |
| 254 | +join other tables into your query, you can pass their names to ``tables``. |
| 255 | + |
| 256 | +Creating new objects |
| 257 | +==================== |
| 258 | + |
0 commit comments