9.14 Raw querysets
QuerySet.raw takes a statement the program wrote and returns a RawQuerySet, which sends the statement when it is iterated, matches the columns that come back to the model’s fields by name, and makes an instance of each row. The statement and its parameters are kept by a RawQuery, and the rows are turned into instances by RawModelIterable.
Now and then a program has a statement that the methods of a queryset will not say, and still wants what a queryset gives: instances of a model, ready to be saved or handed to a template. raw is for that, and what it returns is a raw queryset, an instance of RawQuerySet. Its difficulty is that Django does not read the statement. A Query knows which columns it selects because it chose them. Of a statement handed over as text Django knows nothing until the database has run it and the cursor can say what came back.
What is particular to a raw queryset follows from that. It cannot be refined, counted or limited in the database, since there is no description of the statement to change. It learns its columns from the cursor. And what a row becomes is settled by comparing names: a column named as a field’s column is that field’s value, a field whose column did not come back is left deferred, and a column that matches no field becomes a plain attribute.
The examples are the shipping line of the chapter’s opening page, QuerySets, where the table of sailors has the columns "id", "name", "ship_id" and "signed_on".
What raw returns
QuerySet.raw makes a RawQuerySet from the statement, the parameters, a dictionary of translations if one is given, and four things of the queryset it was called on: the model, an alias for a database, the fetch mode its instances are to be given (Deferred fields and fetch modes in a queryset), and the prefetch lookups (prefetch_related: related objects in a second query). The alias is the one given as using=..., and failing that the queryset’s own QuerySet.db as it is at that moment (Clones: how a queryset is built). The queryset’s query is not among the four, so a condition already on the queryset, a manager’s own filter included, has no part in the statement that is sent. Veteran is a proxy of Sailor whose manager filters every queryset it hands out:
# harbour/crew/models.py
class VeteranManager(models.Manager):
def get_queryset(self):
return super().get_queryset().filter(signed_on__year__lt=2020)
class Veteran(Sailor):
objects = VeteranManager()
class Meta:
proxy = True
What follows was recorded from a running Django, on SQLite: a line at the left is Python that was run, with its value or the exception it raised after -> where it is an expression; under it are the calls it set going, nested as they were made, with only the calls the passage is about written down and what a call returned after ->; and a line that begins SQL is a statement as Django handed it to its cursor.
len(Veteran.objects.raw('SELECT * FROM crew_sailor')), len(Sailor.objects.filter(name='Ada').raw('SELECT * FROM crew_sailor')) -> (4, 4)
SQL SELECT * FROM crew_sailor
SQL SELECT * FROM crew_sailor
Each raw queryset returned all four sailors: the manager’s filter in the first and the queryset’s in the second went unused.
On a manager, raw is one of the methods a manager class is given from QuerySet: it calls raw on the queryset that the manager’s BaseManager returns (Managers).
RawQuerySet is not a subclass of QuerySet. RawQuerySet keeps the statement and the parameters, the model, the alias as RawQuerySet._db, a dictionary of hints for the router, the translations and the fetch mode, and it starts with no result cache (Evaluation and the result cache). In RawQuerySet it keeps a RawQuery, which it makes from the statement, the parameters and its RawQuerySet.db unless it was passed one. A RawQuery is what a raw queryset has where a queryset has a Query: it holds the text, runs it, and holds the cursor afterwards.
hands = Sailor.objects.raw('SELECT id, name, upper(name) AS shout FROM crew_sailor WHERE ship_id = %s ORDER BY id', [1])
QuerySet.raw('SELECT id, name, upper(name) AS shout FROM crew_sailor WHERE ship_id = %s ORDER BY id', params=[1]) -> a RawQuerySet of Sailor, no result cache
type(hands).__name__, isinstance(hands, QuerySet), hands._result_cache, repr(hands) -> ('RawQuerySet', False, None, '<RawQuerySet: SELECT id, name, upper(name) AS shout FROM crew_sailor WHERE ship_id = 1 ORDER BY id>')
No statement was sent, for the call or for repr. RawQuerySet prints the RawQuery, whose text is the statement with the parameters put into it by Python’s %: text for reading, and not what will be sent.
When a raw queryset is iterated
Making a raw queryset sends nothing and examines nothing: the statement is not parsed. RawQuerySet calls RawQuerySet, which has the two steps of a queryset’s (Evaluation and the result cache). Where there is no result cache it makes the list of everything that RawQuerySet yields, and keeps it; then it follows any prefetch lookups that have not been followed. iterator is a generator over a RawModelIterable made for the raw queryset.
for sailor in hands: seen(sailor)
RawQuerySet.__iter__
RawQuerySet._fetch_all
RawQuerySet.iterator
RawModelIterable.__iter__
RawQuery.__iter__
RawQuery._execute_query
SQL SELECT id, name, upper(name) AS shout FROM crew_sailor WHERE ship_id = %s ORDER BY id with params (1,)
RawQuerySet.resolve_model_init_order
RawQuery.get_columns -> ['id', 'name', 'shout']
-> (['id', 'name'], [0, 1], [('shout', 2)])
Model.from_db('default', ['id', 'name'], [1, 'Ada'], fetch_mode=FETCH_ONE) (Sailor)
Model.from_db('default', ['id', 'name'], [2, 'Bram'], fetch_mode=FETCH_ONE) (Sailor)
the loop's body runs with <Sailor: Ada>
the loop's body runs with <Sailor: Bram>
The statement went to the cursor as the program wrote it, with its placeholder and the parameter beside it. The three lists that RawQuerySet returned are the fields of Sailor that the statement gave a column for, the place of each in a row, and the one column that is no field’s, with its place. Both rows were made into instances before the loop’s body ran once, and the list of them is now the result cache.
A statement that is wrong is not found out where QuerySet.raw is called:
Sailor.objects.raw('SELEKT nothing') -> <RawQuerySet: SELEKT nothing>
list(Sailor.objects.raw('SELEKT nothing')) -> raises OperationalError
SQL SELEKT nothing
The error is raised when the list is asked for, and it is the database’s: on SQLite an OperationalError, for text that cannot be parsed.
RawQuery: the statement and its cursor
A RawQuery holds the text, the parameters and one cursor, that of its latest execution, and keeps no rows. RawQuery is the execution: it adapts the parameters, takes an ordinary cursor from the connection, keeps it as RawQuery.cursor, and has it execute the statement. Two methods call it. RawQuery executes anew each time it is called, and returns an iterator over the cursor. RawQuery executes only where there is no cursor yet, and returns the names of the columns of the result, from the cursor’s description.
names = Sailor.objects.raw('SELECT id, name FROM crew_sailor WHERE name = %s', ['Ada'])
names.columns -> ['id', 'name']
SQL SELECT id, name FROM crew_sailor WHERE name = %s with params ('Ada',)
len(names), repr(names) -> (1, '<RawQuerySet: SELECT id, name FROM crew_sailor WHERE name = Ada>')
SQL SELECT id, name FROM crew_sailor WHERE name = %s with params ('Ada',)
Asked for its columns before anything else, the raw queryset had the statement run for the names alone, and len then had it run again for the rows. The repr in the last answer is the text with a parameter that is a string put into it, which stands there with no quotes round it.
The parameters may be a sequence, a mapping, for placeholders written with names, or None, which is passed on as it is. Each parameter of a sequence or a mapping is first adapted by the backend’s BaseDatabaseOperations, which goes by the Python type of the value alone: a datetime, a date, a time or a decimal.Decimal is given to the method of the backend’s operations that adapts a value of a field of that kind, and anything else is left as it is. How the placeholders and the parameters reach the driver is in Cursors: executing a statement, the query log and execute wrappers.
Which column is whose
RawQuerySet is the list that RawQuery returns, with the translations applied. The dictionary given to QuerySet.raw as translations=... goes from the name of a column in the result to the name it is to be taken as, and a key that names no column of the result is passed over. columns is a cached property: read on a raw queryset whose RawQuery has not yet run the statement, it has the statement run to learn the names, as the recording above shows.
RawQuerySet is a dictionary from column names to fields: each field of the model that has a column, in the model’s order, under the name of that column as the backend’s BaseDatabaseIntrospection gives it. RawQuery passes each name through the same function, which puts a name into lower case on Oracle and changes nothing in the base class. The match is by column and not by the field’s name: the foreign key ship is found under "ship_id", and a translation that is to make a column that field’s has to say "ship_id" too.
RawQuerySet puts the two together and returns the three lists: the attnames of the fields whose columns the statement returned, in the model’s order, the place of each of those in a row, and the columns that match no field, each with its place. A field’s attname is the name of the attribute that holds its value on an instance.
Model.from_db, the place of each in a row, and the columns left over with theirs.Asked afterwards, the raw queryset of the loop and its first instance say the same:
hands.columns, sorted(hands.model_fields), hands[0].shout, sorted(hands[0].get_deferred_fields()) -> (['id', 'name', 'shout'], ['id', 'name', 'ship_id', 'signed_on'], 'ADA', ['ship_id', 'signed_on'])
Because the match is by name alone, a translation can make a column a field’s, and nothing ties the statement to the model’s table:
[sailor.name for sailor in Sailor.objects.raw('SELECT id, name AS label FROM crew_sailor ORDER BY id LIMIT 2', translations={'label': 'name'})] -> ['Ada', 'Bram']
SQL SELECT id, name AS label FROM crew_sailor ORDER BY id LIMIT 2
...
[type(ship).__name__ for ship in Sailor.objects.raw('SELECT * FROM fleet_ship')] -> ['Sailor', 'Sailor']
SQL SELECT * FROM fleet_ship
The second question made Sailors of the rows of the table of ships. That table has an "id" and a "name", which were taken for a sailor’s.
From a row to an instance: RawModelIterable
RawModelIterable is the generator that runs the statement and makes an instance of each row; the lines nested under it in the recording of the loop are its doing.
It begins by making a compiler, the backend’s SQLCompiler, round the RawQuery. Nothing is compiled: the compiler is wanted for the conversion of the driver’s values into Python’s after a statement has run, and a comment in the constructor of RawQuery says that it mirrors a few attributes of a normal query so that a compiler can be used on its results. Then the iterable asks the RawQuery for an iterator, which sends the statement.
RawQuerySet is called, and the first use of its answer is a test: every field of the model’s primary key has to be among the fields the statement returned, the documentation’s reason being that Django identifies an instance by its primary key. Where one is missing the iterable raises FieldDoesNotExist. The statement has been sent by then:
list(Sailor.objects.raw('SELECT name FROM crew_sailor')) -> raises FieldDoesNotExist: Raw query must include the primary key
SQL SELECT name FROM crew_sailor
The values of a column that matches a field are passed through the converters of the backend and of that field, the functions that turn a value as the driver returned it into the one Python is to have, as a queryset’s are; a value in a column that matches none is left as the driver returned it.
From each row the iterable picks the values at the places that resolve_model_init_order gave, and calls Model.from_db with the alias, the list of attnames and those values, the raw queryset’s fetch mode being bound to the call by _get_from_db (From rows to instances: ModelIterable and select_related). Given fewer names than the model has concrete fields, from_db leaves the other fields deferred (An instance and its state). Where the fetch mode tracks peers, the instances that one iteration makes, the instance is added to the list of them and given the list (Deferred fields and fetch modes in a queryset). Each column left over is set on the instance with setattr, under the column’s name. When the iteration ends, however it ends, the iterable closes the cursor of the RawQuery.
A composite primary key
A CompositePrimaryKey has no column of its own, so a raw queryset has to find the key of such a model in the columns of the fields the key is made of. A Berth, in the shipping line’s office, is known by its port and its number there:
# harbour/office/models.py
class Berth(models.Model):
pk = models.CompositePrimaryKey("port_id", "number")
port = models.ForeignKey("fleet.Port", on_delete=models.CASCADE)
number = models.PositiveSmallIntegerField()
metres = models.PositiveSmallIntegerField()
RawQuerySet leaves out a field without a column, so no column of a result is ever matched to "pk". The test for the primary key is made on Options, which for such a key are the fields it is made of: both "port_id" and "number" have to come back.
[(berth.port_id, berth.number, berth.pk) for berth in Berth.objects.raw('SELECT * FROM office_berth ORDER BY number')] -> [(1, 1, (1, 1)), (1, 2, (1, 2))]
SQL SELECT * FROM office_berth ORDER BY number
list(Berth.objects.raw('SELECT port_id, metres FROM office_berth')) -> raises FieldDoesNotExist: Raw query must include the primary key
SQL SELECT port_id, metres FROM office_berth
The key of each Berth in the first answer was not read from a row. It is worked out from the two attributes each time "pk" is read, by the descriptor of the key (The field classes).
What else a raw queryset answers
RawQuerySet and RawQuerySet call RawQuerySet and answer from the list, and RawQuerySet makes a list of the whole raw queryset and indexes that. So an index or a slice evaluates everything, is never made a limit in the statement, and may be negative, which a QuerySet refuses.
RawQuerySet, called by a program, runs the statement and yields each instance without keeping it, and does not follow prefetch lookups. RawQuerySet hands _fetch_all to a thread and then yields from the list (The asynchronous methods of a queryset).
In these lines names is the raw queryset whose columns were asked for above: it selects the "id" and the "name" of Ada, and has been evaluated.
names[-1].name, names[0].signed_on.year -> ('Ada', 2011)
SQL SELECT "crew_sailor"."id", "crew_sailor"."signed_on" FROM "crew_sailor" WHERE "crew_sailor"."id" = %s LIMIT 21 with params (1,)
len(list(names.iterator())), len(names._result_cache) -> (1, 1)
SQL SELECT id, name FROM crew_sailor WHERE name = %s with params ('Ada',)
unkept = Sailor.objects.raw('SELECT id, name FROM crew_sailor')
len(list(unkept.iterator())), unkept._result_cache -> (4, None)
SQL SELECT id, name FROM crew_sailor
A negative index was answered from the list. Reading "signed_on", a column the statement did not return, sent one statement for that field of that instance. iterator on names ran the raw statement again, and the result cache still held its one instance; on a raw queryset that had not been evaluated it ran the statement and left no result cache.
The methods of a QuerySet that refine or count are not there:
Sailor.objects.raw('SELECT * FROM crew_sailor').filter(name='Ada') -> raises AttributeError
Prefetch lookups, copies and the database
RawQuerySet adds prefetch lookups to a copy of the raw queryset, and given None alone it empties the list, as a queryset’s does. RawQuerySet follows them after the list is made, by the same prefetch_related_objects (prefetch_related: related objects in a second query).
[len(s.crew.all()) for s in Ship.objects.raw('SELECT * FROM fleet_ship ORDER BY id').prefetch_related('crew')] -> [2, 2]
SQL SELECT * FROM fleet_ship ORDER BY id
SQL SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on" FROM "crew_sailor" WHERE "crew_sailor"."ship_id" IN (%s, %s) with params (1, 2)
The other way round is refused: a Prefetch raises ValueError when it is given a raw queryset as its queryset.
A raw queryset is made by QuerySet.raw, by RawQuerySet and by RawQuerySet, and the three do not carry the same things over.
RawQuerySet is given by each of the three (RawQuerySet._clone , RawQuerySet.using ).| what it has | made by QuerySet.raw |
made by _clone, which prefetch_related calls |
made by using |
|---|---|---|---|
the RawQuery |
made by QuerySet.raw a new one |
made by _clone, which prefetch_related calls the same object |
made by using a new one for the alias, from RawQuery.chain |
| the alias | made by QuerySet.raw the one given, or the queryset’s QuerySet.db |
made by _clone, which prefetch_related calls the same |
made by using the one given |
| the hints | made by QuerySet.raw none |
made by _clone, which prefetch_related calls the same |
made by using none |
| the fetch mode | made by QuerySet.raw the queryset’s |
made by _clone, which prefetch_related calls FETCH_ONE, whatever the original has |
made by using the same |
| the prefetch lookups | made by QuerySet.raw the queryset’s |
made by _clone, which prefetch_related calls the original’s |
made by using none |
mode(Sailor.objects.fetch_mode(models.FETCH_PEERS).raw('SELECT * FROM crew_sailor'), Sailor.objects.fetch_mode(models.FETCH_PEERS).raw('SELECT * FROM crew_sailor').prefetch_related('ship'), Sailor.objects.fetch_mode(models.FETCH_PEERS).raw('SELECT * FROM crew_sailor').using('default')) -> (FETCH_PEERS, FETCH_ONE, FETCH_PEERS)
Sailor.objects.raw('SELECT * FROM crew_sailor').prefetch_related('ship').using('default')._prefetch_related_lookups -> ()
In the first line mode(...) stands for the fetch mode of each of three raw querysets, by its name: one that raw made from a queryset with FETCH_PEERS, and one each that prefetch_related and using made of such a raw queryset. _clone does not pass the fetch mode on, so any call of prefetch_related on a raw queryset returns one whose fetch mode is FETCH_ONE, and that is the mode its instances are given. using does not copy the prefetch lookups, so called after prefetch_related it drops them, as the second line shows. Neither copy has the result cache of its original.
RawQuerySet.db returns the alias the raw queryset was made with, and with none it asks the router’s ConnectionRouter with the model and the hints. The class has no mark for writing, so by itself it does not ask ConnectionRouter. A raw queryset from QuerySet.raw has an alias in every case, the one that method passed.