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