tests/modeltests/lookup/models.py

   1 """
   2 7. The lookup API
   3
   4 This demonstrates features of the database API.
   5 """
   6
   7 from django.db import models
   8 from django.conf import settings
   9
  10 class Article(models.Model):
  11     headline = models.CharField(max_length=100)
  12     pub_date = models.DateTimeField()
  13     class Meta:
  14         ordering = ('-pub_date', 'headline')
  15
  16     def __unicode__(self):
  17         return self.headline
  18
  19 __test__ = {'API_TESTS':r"""
  20 # Create a couple of Articles.
  21 >>> from datetime import datetime
  22 >>> a1 = Article(headline='Article 1', pub_date=datetime(2005, 7, 26))
  23 >>> a1.save()
  24 >>> a2 = Article(headline='Article 2', pub_date=datetime(2005, 7, 27))
  25 >>> a2.save()
  26 >>> a3 = Article(headline='Article 3', pub_date=datetime(2005, 7, 27))
  27 >>> a3.save()
  28 >>> a4 = Article(headline='Article 4', pub_date=datetime(2005, 7, 28))
  29 >>> a4.save()
  30 >>> a5 = Article(headline='Article 5', pub_date=datetime(2005, 8, 1, 9, 0))
  31 >>> a5.save()
  32 >>> a6 = Article(headline='Article 6', pub_date=datetime(2005, 8, 1, 8, 0))
  33 >>> a6.save()
  34 >>> a7 = Article(headline='Article 7', pub_date=datetime(2005, 7, 27))
  35 >>> a7.save()
  36
  37 # Each QuerySet gets iterator(), which is a generator that "lazily" returns
  38 # results using database-level iteration.
  39 >>> for a in Article.objects.iterator():
  40 ...     print a.headline
  41 Article 5
  42 Article 6
  43 Article 4
  44 Article 2
  45 Article 3
  46 Article 7
  47 Article 1
  48
  49 # iterator() can be used on any QuerySet.
  50 >>> for a in Article.objects.filter(headline__endswith='4').iterator():
  51 ...     print a.headline
  52 Article 4
  53
  54 # count() returns the number of objects matching search criteria.
  55 >>> Article.objects.count()
  56 7L
  57 >>> Article.objects.filter(pub_date__exact=datetime(2005, 7, 27)).count()
  58 3L
  59 >>> Article.objects.filter(headline__startswith='Blah blah').count()
  60 0L
  61
  62 # count() should respect sliced query sets.
  63 >>> articles = Article.objects.all()
  64 >>> articles.count()
  65 7L
  66 >>> articles[:4].count()
  67 4
  68 >>> articles[1:100].count()
  69 6L
  70 >>> articles[10:100].count()
  71 0
  72
  73 # Date and date/time lookups can also be done with strings.
  74 >>> Article.objects.filter(pub_date__exact='2005-07-27 00:00:00').count()
  75 3L
  76
  77 # in_bulk() takes a list of IDs and returns a dictionary mapping IDs
  78 # to objects.
  79 >>> arts = Article.objects.in_bulk([1, 2])
  80 >>> arts[1]
  81 <Article: Article 1>
  82 >>> arts[2]
  83 <Article: Article 2>
  84 >>> Article.objects.in_bulk([3])
  85 {3: <Article: Article 3>}
  86 >>> Article.objects.in_bulk([1000])
  87 {}
  88 >>> Article.objects.in_bulk([])
  89 {}
  90 >>> Article.objects.in_bulk('foo')
  91 Traceback (most recent call last):
  92     ...
  93 AssertionError: in_bulk() must be provided with a list of IDs.
  94 >>> Article.objects.in_bulk()
  95 Traceback (most recent call last):
  96     ...
  97 TypeError: in_bulk() takes exactly 2 arguments (1 given)
  98 >>> Article.objects.in_bulk(headline__startswith='Blah')
  99 Traceback (most recent call last):
 100     ...
 101 TypeError: in_bulk() got an unexpected keyword argument 'headline__startswith'
 102
 103 # values() returns a list of dictionaries instead of object instances -- and
 104 # you can specify which fields you want to retrieve.
 105 >>> Article.objects.values('headline')
 106 [{'headline': u'Article 5'}, {'headline': u'Article 6'}, {'headline': u'Article 4'}, {'headline': u'Article 2'}, {'headline': u'Article 3'}, {'headline': u'Article 7'}, {'headline': u'Article 1'}]
 107 >>> Article.objects.filter(pub_date__exact=datetime(2005, 7, 27)).values('id')
 108 [{'id': 2}, {'id': 3}, {'id': 7}]
 109 >>> list(Article.objects.values('id', 'headline')) == [{'id': 5, 'headline': 'Article 5'}, {'id': 6, 'headline': 'Article 6'}, {'id': 4, 'headline': 'Article 4'}, {'id': 2, 'headline': 'Article 2'}, {'id': 3, 'headline': 'Article 3'}, {'id': 7, 'headline': 'Article 7'}, {'id': 1, 'headline': 'Article 1'}]
 110 True
 111
 112 >>> for d in Article.objects.values('id', 'headline'):
 113 ...     i = d.items()
 114 ...     i.sort()
 115 ...     i
 116 [('headline', u'Article 5'), ('id', 5)]
 117 [('headline', u'Article 6'), ('id', 6)]
 118 [('headline', u'Article 4'), ('id', 4)]
 119 [('headline', u'Article 2'), ('id', 2)]
 120 [('headline', u'Article 3'), ('id', 3)]
 121 [('headline', u'Article 7'), ('id', 7)]
 122 [('headline', u'Article 1'), ('id', 1)]
 123
 124 # You can use values() with iterator() for memory savings, because iterator()
 125 # uses database-level iteration.
 126 >>> for d in Article.objects.values('id', 'headline').iterator():
 127 ...     i = d.items()
 128 ...     i.sort()
 129 ...     i
 130 [('headline', u'Article 5'), ('id', 5)]
 131 [('headline', u'Article 6'), ('id', 6)]
 132 [('headline', u'Article 4'), ('id', 4)]
 133 [('headline', u'Article 2'), ('id', 2)]
 134 [('headline', u'Article 3'), ('id', 3)]
 135 [('headline', u'Article 7'), ('id', 7)]
 136 [('headline', u'Article 1'), ('id', 1)]
 137
 138 # The values() method works with "extra" fields specified in extra(select).
 139 >>> for d in Article.objects.extra(select={'id_plus_one': 'id + 1'}).values('id', 'id_plus_one'):
 140 ...     i = d.items()
 141 ...     i.sort()
 142 ...     i
 143 [('id', 5), ('id_plus_one', 6)]
 144 [('id', 6), ('id_plus_one', 7)]
 145 [('id', 4), ('id_plus_one', 5)]
 146 [('id', 2), ('id_plus_one', 3)]
 147 [('id', 3), ('id_plus_one', 4)]
 148 [('id', 7), ('id_plus_one', 8)]
 149 [('id', 1), ('id_plus_one', 2)]
 150 >>> data = {'id_plus_one': 'id+1', 'id_plus_two': 'id+2', 'id_plus_three': 'id+3',
 151 ...         'id_plus_four': 'id+4', 'id_plus_five': 'id+5', 'id_plus_six': 'id+6',
 152 ...         'id_plus_seven': 'id+7', 'id_plus_eight': 'id+8'}
 153 >>> result = list(Article.objects.filter(id=1).extra(select=data).values(*data.keys()))[0]
 154 >>> result = result.items()
 155 >>> result.sort()
 156 >>> result
 157 [('id_plus_eight', 9), ('id_plus_five', 6), ('id_plus_four', 5), ('id_plus_one', 2), ('id_plus_seven', 8), ('id_plus_six', 7), ('id_plus_three', 4), ('id_plus_two', 3)]
 158
 159 # However, an exception FieldDoesNotExist will be thrown if you specify a
 160 # non-existent field name in values() (a field that is neither in the model
 161 # nor in extra(select)).
 162 >>> Article.objects.extra(select={'id_plus_one': 'id + 1'}).values('id', 'id_plus_two')
 163 Traceback (most recent call last):
 164     ...
 165 FieldError: Cannot resolve keyword 'id_plus_two' into field. Choices are: headline, id, id_plus_one, pub_date
 166
 167 # If you don't specify field names to values(), all are returned.
 168 >>> list(Article.objects.filter(id=5).values()) == [{'id': 5, 'headline': 'Article 5', 'pub_date': datetime(2005, 8, 1, 9, 0)}]
 169 True
 170
 171 # values_list() is similar to values(), except that the results are returned as
 172 # a list of tuples, rather than a list of dictionaries. Within each tuple, the
 173 # order of the elemnts is the same as the order of fields in the values_list()
 174 # call.
 175 >>> Article.objects.values_list('headline')
 176 [(u'Article 5',), (u'Article 6',), (u'Article 4',), (u'Article 2',), (u'Article 3',), (u'Article 7',), (u'Article 1',)]
 177
 178 >>> Article.objects.values_list('id').order_by('id')
 179 [(1,), (2,), (3,), (4,), (5,), (6,), (7,)]
 180 >>> Article.objects.values_list('id', flat=True).order_by('id')
 181 [1, 2, 3, 4, 5, 6, 7]
 182
 183 >>> Article.objects.extra(select={'id_plus_one': 'id+1'}).order_by('id').values_list('id')
 184 [(1,), (2,), (3,), (4,), (5,), (6,), (7,)]
 185 >>> Article.objects.extra(select={'id_plus_one': 'id+1'}).order_by('id').values_list('id_plus_one', 'id')
 186 [(2, 1), (3, 2), (4, 3), (5, 4), (6, 5), (7, 6), (8, 7)]
 187 >>> Article.objects.extra(select={'id_plus_one': 'id+1'}).order_by('id').values_list('id', 'id_plus_one')
 188 [(1, 2), (2, 3), (3, 4), (4, 5), (5, 6), (6, 7), (7, 8)]
 189
 190 >>> Article.objects.values_list('id', 'headline', flat=True)
 191 Traceback (most recent call last):
 192 ...
 193 TypeError: 'flat' is not valid when values_list is called with more than one field.
 194
 195 # Every DateField and DateTimeField creates get_next_by_FOO() and
 196 # get_previous_by_FOO() methods.
 197 # In the case of identical date values, these methods will use the ID as a
 198 # fallback check. This guarantees that no records are skipped or duplicated.
 199 >>> a1.get_next_by_pub_date()
 200 <Article: Article 2>
 201 >>> a2.get_next_by_pub_date()
 202 <Article: Article 3>
 203 >>> a2.get_next_by_pub_date(headline__endswith='6')
 204 <Article: Article 6>
 205 >>> a3.get_next_by_pub_date()
 206 <Article: Article 7>
 207 >>> a4.get_next_by_pub_date()
 208 <Article: Article 6>
 209 >>> a5.get_next_by_pub_date()
 210 Traceback (most recent call last):
 211     ...
 212 DoesNotExist: Article matching query does not exist.
 213 >>> a6.get_next_by_pub_date()
 214 <Article: Article 5>
 215 >>> a7.get_next_by_pub_date()
 216 <Article: Article 4>
 217
 218 >>> a7.get_previous_by_pub_date()
 219 <Article: Article 3>
 220 >>> a6.get_previous_by_pub_date()
 221 <Article: Article 4>
 222 >>> a5.get_previous_by_pub_date()
 223 <Article: Article 6>
 224 >>> a4.get_previous_by_pub_date()
 225 <Article: Article 7>
 226 >>> a3.get_previous_by_pub_date()
 227 <Article: Article 2>
 228 >>> a2.get_previous_by_pub_date()
 229 <Article: Article 1>
 230
 231 # Underscores and percent signs have special meaning in the underlying
 232 # SQL code, but Django handles the quoting of them automatically.
 233 >>> a8 = Article(headline='Article_ with underscore', pub_date=datetime(2005, 11, 20))
 234 >>> a8.save()
 235 >>> Article.objects.filter(headline__startswith='Article')
 236 [<Article: Article_ with underscore>, <Article: Article 5>, <Article: Article 6>, <Article: Article 4>, <Article: Article 2>, <Article: Article 3>, <Article: Article 7>, <Article: Article 1>]
 237 >>> Article.objects.filter(headline__startswith='Article_')
 238 [<Article: Article_ with underscore>]
 239
 240 >>> a9 = Article(headline='Article% with percent sign', pub_date=datetime(2005, 11, 21))
 241 >>> a9.save()
 242 >>> Article.objects.filter(headline__startswith='Article')
 243 [<Article: Article% with percent sign>, <Article: Article_ with underscore>, <Article: Article 5>, <Article: Article 6>, <Article: Article 4>, <Article: Article 2>, <Article: Article 3>, <Article: Article 7>, <Article: Article 1>]
 244 >>> Article.objects.filter(headline__startswith='Article%')
 245 [<Article: Article% with percent sign>]
 246
 247 # exclude() is the opposite of filter() when doing lookups:
 248 >>> Article.objects.filter(headline__contains='Article').exclude(headline__contains='with')
 249 [<Article: Article 5>, <Article: Article 6>, <Article: Article 4>, <Article: Article 2>, <Article: Article 3>, <Article: Article 7>, <Article: Article 1>]
 250 >>> Article.objects.exclude(headline__startswith="Article_")
 251 [<Article: Article% with percent sign>, <Article: Article 5>, <Article: Article 6>, <Article: Article 4>, <Article: Article 2>, <Article: Article 3>, <Article: Article 7>, <Article: Article 1>]
 252 >>> Article.objects.exclude(headline="Article 7")
 253 [<Article: Article% with percent sign>, <Article: Article_ with underscore>, <Article: Article 5>, <Article: Article 6>, <Article: Article 4>, <Article: Article 2>, <Article: Article 3>, <Article: Article 1>]
 254
 255 # Backslashes also have special meaning in the underlying SQL code, but Django
 256 # automatically quotes them appropriately.
 257 >>> a10 = Article(headline='Article with \\ backslash', pub_date=datetime(2005, 11, 22))
 258 >>> a10.save()
 259 >>> Article.objects.filter(headline__contains='\\')
 260 [<Article: Article with \ backslash>]
 261
 262 # none() returns an EmptyQuerySet that behaves like any other QuerySet object
 263 >>> Article.objects.none()
 264 []
 265 >>> Article.objects.none().filter(headline__startswith='Article')
 266 []
 267 >>> Article.objects.filter(headline__startswith='Article').none()
 268 []
 269 >>> Article.objects.none().count()
 270 0
 271 >>> [article for article in Article.objects.none().iterator()]
 272 []
 273
 274 # using __in with an empty list should return an empty query set
 275 >>> Article.objects.filter(id__in=[])
 276 []
 277
 278 >>> Article.objects.exclude(id__in=[])
 279 [<Article: Article with \ backslash>, <Article: Article% with percent sign>, <Article: Article_ with underscore>, <Article: Article 5>, <Article: Article 6>, <Article: Article 4>, <Article: Article 2>, <Article: Article 3>, <Article: Article 7>, <Article: Article 1>]
 280
 281 # Programming errors are pointed out with nice error messages
 282 >>> Article.objects.filter(pub_date_year='2005').count()
 283 Traceback (most recent call last):
 284     ...
 285 FieldError: Cannot resolve keyword 'pub_date_year' into field. Choices are: headline, id, pub_date
 286
 287 >>> Article.objects.filter(headline__starts='Article')
 288 Traceback (most recent call last):
 289     ...
 290 FieldError: Join on field 'headline' not permitted.
 291
 292 # Create some articles with a bit more interesting headlines for testing field lookups:
 293 >>> now = datetime.now()
 294 >>> for a in Article.objects.all():
 295 ...     a.delete()
 296 >>> a1 = Article(pub_date=now, headline='f')
 297 >>> a1.save()
 298 >>> a2 = Article(pub_date=now, headline='fo')
 299 >>> a2.save()
 300 >>> a3 = Article(pub_date=now, headline='foo')
 301 >>> a3.save()
 302 >>> a4 = Article(pub_date=now, headline='fooo')
 303 >>> a4.save()
 304 >>> a5 = Article(pub_date=now, headline='hey-Foo')
 305 >>> a5.save()
 306
 307 # zero-or-more
 308 >>> Article.objects.filter(headline__regex=r'fo*')
 309 [<Article: f>, <Article: fo>, <Article: foo>, <Article: fooo>]
 310 >>> Article.objects.filter(headline__iregex=r'fo*')
 311 [<Article: f>, <Article: fo>, <Article: foo>, <Article: fooo>, <Article: hey-Foo>]
 312
 313 # one-or-more
 314 >>> Article.objects.filter(headline__regex=r'fo+')
 315 [<Article: fo>, <Article: foo>, <Article: fooo>]
 316
 317 # wildcard
 318 >>> Article.objects.filter(headline__regex=r'fooo?')
 319 [<Article: foo>, <Article: fooo>]
 320
 321 # and some more:
 322 >>> a6 = Article(pub_date=now, headline='bar')
 323 >>> a6.save()
 324 >>> a7 = Article(pub_date=now, headline='AbBa')
 325 >>> a7.save()
 326 >>> a8 = Article(pub_date=now, headline='baz')
 327 >>> a8.save()
 328 >>> a9 = Article(pub_date=now, headline='baxZ')
 329 >>> a9.save()
 330
 331 # leading anchor
 332 >>> Article.objects.filter(headline__regex=r'^b')
 333 [<Article: bar>, <Article: baxZ>, <Article: baz>]
 334 >>> Article.objects.filter(headline__iregex=r'^a')
 335 [<Article: AbBa>]
 336
 337 # trailing anchor
 338 >>> Article.objects.filter(headline__regex=r'z$')
 339 [<Article: baz>]
 340 >>> Article.objects.filter(headline__iregex=r'z$')
 341 [<Article: baxZ>, <Article: baz>]
 342
 343 # character sets
 344 >>> Article.objects.filter(headline__regex=r'ba[rz]')
 345 [<Article: bar>, <Article: baz>]
 346 >>> Article.objects.filter(headline__regex=r'ba.[RxZ]')
 347 [<Article: baxZ>]
 348 >>> Article.objects.filter(headline__iregex=r'ba[RxZ]')
 349 [<Article: bar>, <Article: baxZ>, <Article: baz>]
 350
 351 # and yet more:
 352 >>> a10 = Article(pub_date=now, headline='foobar')
 353 >>> a10.save()
 354 >>> a11 = Article(pub_date=now, headline='foobaz')
 355 >>> a11.save()
 356 >>> a12 = Article(pub_date=now, headline='ooF')
 357 >>> a12.save()
 358 >>> a13 = Article(pub_date=now, headline='foobarbaz')
 359 >>> a13.save()
 360 >>> a14 = Article(pub_date=now, headline='zoocarfaz')
 361 >>> a14.save()
 362 >>> a15 = Article(pub_date=now, headline='barfoobaz')
 363 >>> a15.save()
 364 >>> a16 = Article(pub_date=now, headline='bazbaRFOO')
 365 >>> a16.save()
 366
 367 # alternation
 368 >>> Article.objects.filter(headline__regex=r'oo(f|b)')
 369 [<Article: barfoobaz>, <Article: foobar>, <Article: foobarbaz>, <Article: foobaz>]
 370 >>> Article.objects.filter(headline__iregex=r'oo(f|b)')
 371 [<Article: barfoobaz>, <Article: foobar>, <Article: foobarbaz>, <Article: foobaz>, <Article: ooF>]
 372 >>> Article.objects.filter(headline__regex=r'^foo(f|b)')
 373 [<Article: foobar>, <Article: foobarbaz>, <Article: foobaz>]
 374
 375 # greedy matching
 376 >>> Article.objects.filter(headline__regex=r'b.*az')
 377 [<Article: barfoobaz>, <Article: baz>, <Article: bazbaRFOO>, <Article: foobarbaz>, <Article: foobaz>]
 378 >>> Article.objects.filter(headline__iregex=r'b.*ar')
 379 [<Article: bar>, <Article: barfoobaz>, <Article: bazbaRFOO>, <Article: foobar>, <Article: foobarbaz>]
 380 """}
 381
 382
 383 if settings.DATABASE_ENGINE not in ('mysql', 'mysql_old'):
 384     __test__['API_TESTS'] += r"""
 385 # grouping and backreferences
 386 >>> Article.objects.filter(headline__regex=r'b(.).*b\1')
 387 [<Article: barfoobaz>, <Article: bazbaRFOO>, <Article: foobarbaz>]
 388 """