For almost all the features of Django, there are ways to modify and expand. It is extremely rare that you need to rewrite a substantial part of the Django functionality just to change the operation of a tool. For example, if you want to change the appearance of a form, you can abandon the default appearance and create your own field, or even just use your own HTML . In both cases, you win by the spectrum of possibilities, while retaining all the other advantages of the form library.

This extension can be represented as a curve. At one end is a basic option that is easy to write but limits control options. At the opposite end of the curve there will be a specially created class for forms with static HTML code that provides a greater level of control, but which is even more difficult to create. In the example with the creation of forms, this curve will be quite smooth, since for the whole range of functions of the library of forms there are opportunities for the gradual replacement of basic options with your own custom extensions.

Prior to the release of Django 1.2 for ORM, such a curve had a similar appearance, with one exception: a significant leap at the end. This jump appeared due to the fact that if you need to create a non-standard SQL queryrequired to go beyond ORM. To use certain ORM functionality, the user would have to recreate it on his own, although this is not in itself a disaster. In Django 1.2, the Model.objects.raw () method has been added, which solves this problem and thus smooths this curve for ORM.

Old way

Before the release of Django 1.2, if necessary, create an SQL query, you had to write something like this:

from django.db import connection
from library.models import Author 
cursor = connection.cursor ()
query = "SELECT * FROM library_author"
cursor.execute (query)
results = cursor.fetchall () 
authors = []
for result in results:
    author = Author (* result)
    authors.append (author)

Not that it was absolutely terrible, but here we lose access to the ORM functionality in everything that does not concern the creation of SQL queries. In particular, automatic transformation of query results into a model instance is not available. Moderately time-consuming ways to restore lost functionality, of course, exist, but in fact it will be an invention of the bicycle.

New way

In Django 1.2, to create a direct SQL query, you need to write the following:

from library.models import Author 
query = "SELECT * FROM library_author"
authors = Author.objects.raw (query)

authors here will be an instance of RawQuerySet. RawQuerySet is a lot like QuerySet. In particular, the similarity is that it is an iterable object that returns an instance of the model from the query results with each iteration. It differs from QuerySet in that it cannot be embedded in a chain. But this is not important here, as requests are no longer automatically generated.

As with the  cursor database, we can pass a set of query parameters, Django carefully screens them.

query = "SELECT * FROM library_author WHERE first_name =% s"
params = ('bob',)
authors = Author.objects.raw (query, params)

So it's great! The SQL code is protected from attacks, we have an instance of the model that we wanted, and no invention of the bicycle.

“But that's not all!”

Like most Django tools, the raw () method leaves room for additional functions in uncomfortable situations or for particularly complex queries:

Independent field order

For the Model.objects.raw () method, it doesn't matter in which order the fields are returned upon request. The only thing that matters is whether the names of the request fields correspond to the fields in the model.

# All of these queries will work the same
Author.objects.raw ("SELECT * FROM library_author")
Author.objects.raw ("SELECT id, first_name, last_name FROM library_author")
Author.objects.raw ("SELECT last_name, id, first_name FROM library_author")

Annotations

If, in response to a query, we get fields that do not exist in the model class, they are added as annotations to those model instances returned by the RawQueryset method. This makes it easy to use all the advantages of actions or calculations, the implementation of which is more efficient at the database level.

>>> authors = Author.objects.raw ("SELECT *, age (birth_date) as age FROM library_authors")
>>> for author in authors:
... print "% s is% s." % (author.first_name, author.age)
John is 37.
Jane is 42.
...

Defining the relationship between model and query fields

If for  some reason it is not possible to precisely match the names of the query field and the name of the model field, the Model.objects.raw () method provides the ability to specify it manually.

To map query fields to model fields, you just need to use a dictionary containing the required matches as one of the parameters of the raw () method. Correspondence needs to be determined only for those fields that could not be compared with the fields of the model.

field_map = {'first': 'first_name', 'last': 'last_name'}
    query = 'SELECT id, first_name AS first, last_name as last FROM library_author'
    authors = Author.objects.raw (query, translations = field_map)

Pending fields

Those fields that are assumed in the model but are not returned by the request are marked as pending . They will be filled out only upon request to the model instance field. This is useful in cases where the data is not requested from the “real” table for the model, or when the tables themselves are large enough. Here it must be borne in mind that the primary key cannot be deferred and must be returned by all requests. If the query does not return the primary key, an InvalidQuery exception will be thrown.

Limitations

There are some restrictions on the actions of the raw () method. The most significant of these is that the raw () method only supports SELECT queries. If you try to use any other type of query, an InvalidQuery exception will be thrown. Initially, this was done for the purpose of protection, but in part it was done this way because it makes no sense to return an instance of the model for anything other than a query of type SELECT. Changing data using direct SQL is the last thing worth doing with Django. In order not to provoke these actions, we in no way want to make them more convenient for the user. If you need to use direct SQL queries in addition to a SELECT query, you always have the opportunity to create a database cursor  and work from there.

That's all

That's it. In version Django 1.2, it is significantly simplified to work with SQL queries where necessary. The curve we talked about above has a much smoother look. The official documentation for this feature can be found in  the SQL section .