| 1 |
from datetime import date |
| 2 |
from decimal import Decimal |
| 3 |
|
| 4 |
from django.core.exceptions import FieldDoesNotExist |
| 5 |
from django.db.models.query import RawQuerySet |
| 6 |
from django.test import TestCase, skipUnlessDBFeature |
| 7 |
|
| 8 |
from .models import ( |
| 9 |
Author, |
| 10 |
Book, |
| 11 |
BookFkAsPk, |
| 12 |
Coffee, |
| 13 |
FriendlyAuthor, |
| 14 |
MixedCaseIDColumn, |
| 15 |
Reviewer, |
| 16 |
) |
| 17 |
|
| 18 |
|
| 19 |
class RawQueryTests(TestCase): |
| 20 |
@classmethod |
| 21 |
def setUpTestData(cls): |
| 22 |
cls.a1 = Author.objects.create( |
| 23 |
first_name="Joe", last_name="Smith", dob=date(1950, 9, 20) |
| 24 |
) |
| 25 |
cls.a2 = Author.objects.create( |
| 26 |
first_name="Jill", last_name="Doe", dob=date(1920, 4, 2) |
| 27 |
) |
| 28 |
cls.a3 = Author.objects.create( |
| 29 |
first_name="Bob", last_name="Smith", dob=date(1986, 1, 25) |
| 30 |
) |
| 31 |
cls.a4 = Author.objects.create( |
| 32 |
first_name="Bill", last_name="Jones", dob=date(1932, 5, 10) |
| 33 |
) |
| 34 |
cls.b1 = Book.objects.create( |
| 35 |
title="The awesome book", |
| 36 |
author=cls.a1, |
| 37 |
paperback=False, |
| 38 |
opening_line=( |
| 39 |
"It was a bright cold day in April and the clocks were striking " |
| 40 |
"thirteen." |
| 41 |
), |
| 42 |
) |
| 43 |
cls.b2 = Book.objects.create( |
| 44 |
title="The horrible book", |
| 45 |
author=cls.a1, |
| 46 |
paperback=True, |
| 47 |
opening_line=( |
| 48 |
"On an evening in the latter part of May a middle-aged man " |
| 49 |
"was walking homeward from Shaston to the village of Marlott, " |
| 50 |
"in the adjoining Vale of Blakemore, or Blackmoor." |
| 51 |
), |
| 52 |
) |
| 53 |
cls.b3 = Book.objects.create( |
| 54 |
title="Another awesome book", |
| 55 |
author=cls.a1, |
| 56 |
paperback=False, |
| 57 |
opening_line="A squat gray building of only thirty-four stories.", |
| 58 |
) |
| 59 |
cls.b4 = Book.objects.create( |
| 60 |
title="Some other book", |
| 61 |
author=cls.a3, |
| 62 |
paperback=True, |
| 63 |
opening_line="It was the day my grandmother exploded.", |
| 64 |
) |
| 65 |
cls.c1 = Coffee.objects.create(brand="dunkin doughnuts") |
| 66 |
cls.c2 = Coffee.objects.create(brand="starbucks") |
| 67 |
cls.r1 = Reviewer.objects.create() |
| 68 |
cls.r2 = Reviewer.objects.create() |
| 69 |
cls.r1.reviewed.add(cls.b2, cls.b3, cls.b4) |
| 70 |
|
| 71 |
def assertSuccessfulRawQuery( |
| 72 |
self, |
| 73 |
model, |
| 74 |
query, |
| 75 |
expected_results, |
| 76 |
expected_annotations=(), |
| 77 |
params=[], |
| 78 |
translations=None, |
| 79 |
): |
| 80 |
""" |
| 81 |
Execute the passed query against the passed model and check the output |
| 82 |
""" |
| 83 |
results = list( |
| 84 |
model.objects.raw(query, params=params, translations=translations) |
| 85 |
) |
| 86 |
self.assertProcessed(model, results, expected_results, expected_annotations) |
| 87 |
self.assertAnnotations(results, expected_annotations) |
| 88 |
|
| 89 |
def assertProcessed(self, model, results, orig, expected_annotations=()): |
| 90 |
""" |
| 91 |
Compare the results of a raw query against expected results |
| 92 |
""" |
| 93 |
self.assertEqual(len(results), len(orig)) |
| 94 |
for index, item in enumerate(results): |
| 95 |
orig_item = orig[index] |
| 96 |
for annotation in expected_annotations: |
| 97 |
setattr(orig_item, *annotation) |
| 98 |
|
| 99 |
for field in model._meta.fields: |
| 100 |
# All values on the model are equal |
| 101 |
self.assertEqual( |
| 102 |
getattr(item, field.attname), getattr(orig_item, field.attname) |
| 103 |
) |
| 104 |
# This includes checking that they are the same type |
| 105 |
self.assertEqual( |
| 106 |
type(getattr(item, field.attname)), |
| 107 |
type(getattr(orig_item, field.attname)), |
| 108 |
) |
| 109 |
|
| 110 |
def assertNoAnnotations(self, results): |
| 111 |
""" |
| 112 |
The results of a raw query contain no annotations |
| 113 |
""" |
| 114 |
self.assertAnnotations(results, ()) |
| 115 |
|
| 116 |
def assertAnnotations(self, results, expected_annotations): |
| 117 |
""" |
| 118 |
The passed raw query results contain the expected annotations |
| 119 |
""" |
| 120 |
if expected_annotations: |
| 121 |
for index, result in enumerate(results): |
| 122 |
annotation, value = expected_annotations[index] |
| 123 |
self.assertTrue(hasattr(result, annotation)) |
| 124 |
self.assertEqual(getattr(result, annotation), value) |
| 125 |
|
| 126 |
def test_rawqueryset_repr(self): |
| 127 |
queryset = RawQuerySet(raw_query="SELECT * FROM raw_query_author") |
| 128 |
self.assertEqual( |
| 129 |
repr(queryset), "<RawQuerySet: SELECT * FROM raw_query_author>" |
| 130 |
) |
| 131 |
self.assertEqual( |
| 132 |
repr(queryset.query), "<RawQuery: SELECT * FROM raw_query_author>" |
| 133 |
) |
| 134 |
|
| 135 |
def test_simple_raw_query(self): |
| 136 |
""" |
| 137 |
Basic test of raw query with a simple database query |
| 138 |
""" |
| 139 |
query = "SELECT * FROM raw_query_author" |
| 140 |
authors = Author.objects.all() |
| 141 |
self.assertSuccessfulRawQuery(Author, query, authors) |
| 142 |
|
| 143 |
def test_raw_query_lazy(self): |
| 144 |
""" |
| 145 |
Raw queries are lazy: they aren't actually executed until they're |
| 146 |
iterated over. |
| 147 |
""" |
| 148 |
q = Author.objects.raw("SELECT * FROM raw_query_author") |
| 149 |
self.assertIsNone(q.query.cursor) |
| 150 |
list(q) |
| 151 |
self.assertIsNotNone(q.query.cursor) |
| 152 |
|
| 153 |
def test_FK_raw_query(self): |
| 154 |
""" |
| 155 |
Test of a simple raw query against a model containing a foreign key |
| 156 |
""" |
| 157 |
query = "SELECT * FROM raw_query_book" |
| 158 |
books = Book.objects.all() |
| 159 |
self.assertSuccessfulRawQuery(Book, query, books) |
| 160 |
|
| 161 |
def test_db_column_handler(self): |
| 162 |
""" |
| 163 |
Test of a simple raw query against a model containing a field with |
| 164 |
db_column defined. |
| 165 |
""" |
| 166 |
query = "SELECT * FROM raw_query_coffee" |
| 167 |
coffees = Coffee.objects.all() |
| 168 |
self.assertSuccessfulRawQuery(Coffee, query, coffees) |
| 169 |
|
| 170 |
def test_pk_with_mixed_case_db_column(self): |
| 171 |
""" |
| 172 |
A raw query with a model that has a pk db_column with mixed case. |
| 173 |
""" |
| 174 |
query = "SELECT * FROM raw_query_mixedcaseidcolumn" |
| 175 |
queryset = MixedCaseIDColumn.objects.all() |
| 176 |
self.assertSuccessfulRawQuery(MixedCaseIDColumn, query, queryset) |
| 177 |
|
| 178 |
def test_order_handler(self): |
| 179 |
"""Raw query tolerates columns being returned in any order.""" |
| 180 |
selects = ( |
| 181 |
("dob, last_name, first_name, id"), |
| 182 |
("last_name, dob, first_name, id"), |
| 183 |
("first_name, last_name, dob, id"), |
| 184 |
) |
| 185 |
|
| 186 |
for select in selects: |
| 187 |
query = "SELECT %s FROM raw_query_author" % select |
| 188 |
authors = Author.objects.all() |
| 189 |
self.assertSuccessfulRawQuery(Author, query, authors) |
| 190 |
|
| 191 |
def test_translations(self): |
| 192 |
""" |
| 193 |
Test of raw query's optional ability to translate unexpected result |
| 194 |
column names to specific model fields |
| 195 |
""" |
| 196 |
query = ( |
| 197 |
"SELECT first_name AS first, last_name AS last, dob, id " |
| 198 |
"FROM raw_query_author" |
| 199 |
) |
| 200 |
translations = {"first": "first_name", "last": "last_name"} |
| 201 |
authors = Author.objects.all() |
| 202 |
self.assertSuccessfulRawQuery(Author, query, authors, translations=translations) |
| 203 |
|
| 204 |
def test_params(self): |
| 205 |
""" |
| 206 |
Test passing optional query parameters |
| 207 |
""" |
| 208 |
query = "SELECT * FROM raw_query_author WHERE first_name = %s" |
| 209 |
author = Author.objects.all()[2] |
| 210 |
params = [author.first_name] |
| 211 |
qset = Author.objects.raw(query, params=params) |
| 212 |
results = list(qset) |
| 213 |
self.assertProcessed(Author, results, [author]) |
| 214 |
self.assertNoAnnotations(results) |
| 215 |
self.assertEqual(len(results), 1) |
| 216 |
self.assertIsInstance(repr(qset), str) |
| 217 |
|
| 218 |
def test_params_none(self): |
| 219 |
query = "SELECT * FROM raw_query_author WHERE first_name like 'J%'" |
| 220 |
qset = Author.objects.raw(query, params=None) |
| 221 |
self.assertEqual(len(qset), 2) |
| 222 |
|
| 223 |
def test_escaped_percent(self): |
| 224 |
query = "SELECT * FROM raw_query_author WHERE first_name like 'J%%'" |
| 225 |
qset = Author.objects.raw(query) |
| 226 |
self.assertEqual(len(qset), 2) |
| 227 |
|
| 228 |
@skipUnlessDBFeature("supports_paramstyle_pyformat") |
| 229 |
def test_pyformat_params(self): |
| 230 |
""" |
| 231 |
Test passing optional query parameters |
| 232 |
""" |
| 233 |
query = "SELECT * FROM raw_query_author WHERE first_name = %(first)s" |
| 234 |
author = Author.objects.all()[2] |
| 235 |
params = {"first": author.first_name} |
| 236 |
qset = Author.objects.raw(query, params=params) |
| 237 |
results = list(qset) |
| 238 |
self.assertProcessed(Author, results, [author]) |
| 239 |
self.assertNoAnnotations(results) |
| 240 |
self.assertEqual(len(results), 1) |
| 241 |
self.assertIsInstance(repr(qset), str) |
| 242 |
|
| 243 |
def test_query_representation(self): |
| 244 |
""" |
| 245 |
Test representation of raw query with parameters |
| 246 |
""" |
| 247 |
query = "SELECT * FROM raw_query_author WHERE last_name = %(last)s" |
| 248 |
qset = Author.objects.raw(query, {"last": "foo"}) |
| 249 |
self.assertEqual( |
| 250 |
repr(qset), |
| 251 |
"<RawQuerySet: SELECT * FROM raw_query_author WHERE last_name = foo>", |
| 252 |
) |
| 253 |
self.assertEqual( |
| 254 |
repr(qset.query), |
| 255 |
"<RawQuery: SELECT * FROM raw_query_author WHERE last_name = foo>", |
| 256 |
) |
| 257 |
|
| 258 |
query = "SELECT * FROM raw_query_author WHERE last_name = %s" |
| 259 |
qset = Author.objects.raw(query, {"foo"}) |
| 260 |
self.assertEqual( |
| 261 |
repr(qset), |
| 262 |
"<RawQuerySet: SELECT * FROM raw_query_author WHERE last_name = foo>", |
| 263 |
) |
| 264 |
self.assertEqual( |
| 265 |
repr(qset.query), |
| 266 |
"<RawQuery: SELECT * FROM raw_query_author WHERE last_name = foo>", |
| 267 |
) |
| 268 |
|
| 269 |
def test_many_to_many(self): |
| 270 |
""" |
| 271 |
Test of a simple raw query against a model containing a m2m field |
| 272 |
""" |
| 273 |
query = "SELECT * FROM raw_query_reviewer" |
| 274 |
reviewers = Reviewer.objects.all() |
| 275 |
self.assertSuccessfulRawQuery(Reviewer, query, reviewers) |
| 276 |
|
| 277 |
def test_extra_conversions(self): |
| 278 |
"""Extra translations are ignored.""" |
| 279 |
query = "SELECT * FROM raw_query_author" |
| 280 |
translations = {"something": "else"} |
| 281 |
authors = Author.objects.all() |
| 282 |
self.assertSuccessfulRawQuery(Author, query, authors, translations=translations) |
| 283 |
|
| 284 |
def test_missing_fields(self): |
| 285 |
query = "SELECT id, first_name, dob FROM raw_query_author" |
| 286 |
for author in Author.objects.raw(query): |
| 287 |
self.assertIsNotNone(author.first_name) |
| 288 |
# last_name isn't given, but it will be retrieved on demand |
| 289 |
self.assertIsNotNone(author.last_name) |
| 290 |
|
| 291 |
def test_missing_fields_without_PK(self): |
| 292 |
query = "SELECT first_name, dob FROM raw_query_author" |
| 293 |
msg = "Raw query must include the primary key" |
| 294 |
with self.assertRaisesMessage(FieldDoesNotExist, msg): |
| 295 |
list(Author.objects.raw(query)) |
| 296 |
|
| 297 |
def test_annotations(self): |
| 298 |
query = ( |
| 299 |
"SELECT a.*, count(b.id) as book_count " |
| 300 |
"FROM raw_query_author a " |
| 301 |
"LEFT JOIN raw_query_book b ON a.id = b.author_id " |
| 302 |
"GROUP BY a.id, a.first_name, a.last_name, a.dob ORDER BY a.id" |
| 303 |
) |
| 304 |
expected_annotations = ( |
| 305 |
("book_count", 3), |
| 306 |
("book_count", 0), |
| 307 |
("book_count", 1), |
| 308 |
("book_count", 0), |
| 309 |
) |
| 310 |
authors = Author.objects.order_by("pk") |
| 311 |
self.assertSuccessfulRawQuery(Author, query, authors, expected_annotations) |
| 312 |
|
| 313 |
def test_white_space_query(self): |
| 314 |
query = " SELECT * FROM raw_query_author" |
| 315 |
authors = Author.objects.all() |
| 316 |
self.assertSuccessfulRawQuery(Author, query, authors) |
| 317 |
|
| 318 |
def test_multiple_iterations(self): |
| 319 |
query = "SELECT * FROM raw_query_author" |
| 320 |
normal_authors = Author.objects.all() |
| 321 |
raw_authors = Author.objects.raw(query) |
| 322 |
|
| 323 |
# First Iteration |
| 324 |
first_iterations = 0 |
| 325 |
for index, raw_author in enumerate(raw_authors): |
| 326 |
self.assertEqual(normal_authors[index], raw_author) |
| 327 |
first_iterations += 1 |
| 328 |
|
| 329 |
# Second Iteration |
| 330 |
second_iterations = 0 |
| 331 |
for index, raw_author in enumerate(raw_authors): |
| 332 |
self.assertEqual(normal_authors[index], raw_author) |
| 333 |
second_iterations += 1 |
| 334 |
|
| 335 |
self.assertEqual(first_iterations, second_iterations) |
| 336 |
|
| 337 |
def test_get_item(self): |
| 338 |
# Indexing on RawQuerySets |
| 339 |
query = "SELECT * FROM raw_query_author ORDER BY id ASC" |
| 340 |
third_author = Author.objects.raw(query)[2] |
| 341 |
self.assertEqual(third_author.first_name, "Bob") |
| 342 |
|
| 343 |
first_two = Author.objects.raw(query)[0:2] |
| 344 |
self.assertEqual(len(first_two), 2) |
| 345 |
|
| 346 |
with self.assertRaises(TypeError): |
| 347 |
Author.objects.raw(query)["test"] |
| 348 |
|
| 349 |
def test_inheritance(self): |
| 350 |
f = FriendlyAuthor.objects.create( |
| 351 |
first_name="Wesley", last_name="Chun", dob=date(1962, 10, 28) |
| 352 |
) |
| 353 |
query = "SELECT * FROM raw_query_friendlyauthor" |
| 354 |
self.assertEqual([o.pk for o in FriendlyAuthor.objects.raw(query)], [f.pk]) |
| 355 |
|
| 356 |
def test_query_count(self): |
| 357 |
self.assertNumQueries( |
| 358 |
1, list, Author.objects.raw("SELECT * FROM raw_query_author") |
| 359 |
) |
| 360 |
|
| 361 |
def test_subquery_in_raw_sql(self): |
| 362 |
list( |
| 363 |
Book.objects.raw( |
| 364 |
"SELECT id FROM " |
| 365 |
"(SELECT * FROM raw_query_book WHERE paperback IS NOT NULL) sq" |
| 366 |
) |
| 367 |
) |
| 368 |
|
| 369 |
def test_db_column_name_is_used_in_raw_query(self): |
| 370 |
""" |
| 371 |
Regression test that ensures the `column` attribute on the field is |
| 372 |
used to generate the list of fields included in the query, as opposed |
| 373 |
to the `attname`. This is important when the primary key is a |
| 374 |
ForeignKey field because `attname` and `column` are not necessarily the |
| 375 |
same. |
| 376 |
""" |
| 377 |
b = BookFkAsPk.objects.create(book=self.b1) |
| 378 |
self.assertEqual( |
| 379 |
list( |
| 380 |
BookFkAsPk.objects.raw( |
| 381 |
"SELECT not_the_default FROM raw_query_bookfkaspk" |
| 382 |
) |
| 383 |
), |
| 384 |
[b], |
| 385 |
) |
| 386 |
|
| 387 |
def test_decimal_parameter(self): |
| 388 |
c = Coffee.objects.create(brand="starbucks", price=20.5) |
| 389 |
qs = Coffee.objects.raw( |
| 390 |
"SELECT * FROM raw_query_coffee WHERE price >= %s", params=[Decimal(20)] |
| 391 |
) |
| 392 |
self.assertEqual(list(qs), [c]) |
| 393 |
|
| 394 |
def test_result_caching(self): |
| 395 |
with self.assertNumQueries(1): |
| 396 |
books = Book.objects.raw("SELECT * FROM raw_query_book") |
| 397 |
list(books) |
| 398 |
list(books) |
| 399 |
|
| 400 |
def test_iterator(self): |
| 401 |
with self.assertNumQueries(2): |
| 402 |
books = Book.objects.raw("SELECT * FROM raw_query_book") |
| 403 |
list(books.iterator()) |
| 404 |
list(books.iterator()) |
| 405 |
|
| 406 |
def test_bool(self): |
| 407 |
self.assertIs(bool(Book.objects.raw("SELECT * FROM raw_query_book")), True) |
| 408 |
self.assertIs( |
| 409 |
bool(Book.objects.raw("SELECT * FROM raw_query_book WHERE id = 0")), False |
| 410 |
) |
| 411 |
|
| 412 |
def test_len(self): |
| 413 |
self.assertEqual(len(Book.objects.raw("SELECT * FROM raw_query_book")), 4) |
| 414 |
self.assertEqual( |
| 415 |
len(Book.objects.raw("SELECT * FROM raw_query_book WHERE id = 0")), 0 |
| 416 |
) |