Practically for all opportunities of Django there are ways of modification and expansion. It is rarely necessary to rewrite a significant part of the Django functionality only to change the effect of a tool. For example, if you want to change the appearance of a form, you can opt out of the “default” look and create your own field, or even just use your own HTML . In both cases, you win on the spectrum of possibilities, while retaining all the other benefits of the form library.

This empowerment can be represented as a curve. At one end is a basic version that is easy to write, but limits control. 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 also more complex to create. In the example with the creation of forms, this curve will be fairly smooth, since for the whole range of functions of the form library there are possibilities to gradually replace the basic options with their own user extensions.

Before the release of Django version 1.2 for ORM, this curve had a similar appearance, with one exception: a significant jump at the end. This jump appeared due to the fact that, if it was necessary to create a non-standard SQL query, it was necessary to go beyond the limits of ORM. To use a certain ORM functionality, the user would have to create it anew on his own, although this is not in itself a disaster. In the Django 1.2 version, 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 you need to create a 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 the lost functionality, of course, exist, but in fact this will be the 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) 

The authors here will be an instance of RawQuerySet. RawQuerySet is much 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. Its difference from QuerySet is that it cannot be embedded in a chain. But here it is not important, since requests are no longer automatically generated.

As with the use of the database cursor, 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 this is 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 using additional functions in uncomfortable situations or for particularly complex queries:

Independent field order

For the Model.objects.raw () method, it does not matter in which order the fields are returned on request. The only thing that matters is whether the query field names match 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 that the RawQueryset method returns. It easily allows you to use all the benefits of actions or calculations that are more efficiently performed 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.
 ... 

Determining the relationship between model and query fields

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

To match query fields with model fields, you simply need to use a dictionary containing the required matches as one of the parameters of the raw () method. Matches need 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) 

Deferred Fields

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

Restrictions

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 partly it was done this way because it makes no sense to return an instance of the model for anything other than a SELECT query. Changing data with direct SQL is the last thing to do 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 other than a SELECT query, you always have the opportunity to create a database cursor and work from there.

That's all

Well that's all. In Django version 1.2, it is much easier to work with SQL queries where necessary. The curve about which we spoke above has a much smoother appearance. Official documentation for this feature can be found in the SQL section .